Latest Enterprise DNA Initiatives


Standard deviation and median while filtering measures using slicers

I’ve attached the pbix. I have the four slicers I’d like to use: 1) Fiscal Year, 2) Organization, 3) Project, 4) Project Type. @sam.mckay I had a couple questions, that I really thought would be easier. The measure below is saying. Calculate the standard deviation for FactInvoiceTable and the ALLEXCEPT means remove other filters except for ‘DimProject’[DimProj PROJECT_KEY] & DimProject’[DimProj PRIMARY_PROJECT_TYPE]?

stdev_proj_type_combo = CALCULATE(STDEV.P(FactInvoiceTable[TOTAL_INVOICE_PAID_AMOUNT]),ALLEXCEPT(DimProject,'DimProject'[DimProj PROJECT_KEY],'DimProject'[DimProj PRIMARY_PROJECT_TYPE]))

I originally created a measure for each individually, but I thought it was a little inefficient, so I wrote the above filter.

Also, I’m going to go through each total in the below table that is wrong and work through the IF HASONEFILTER exercise. Does that mean that each Measure that has a wrong total, I need to use a IF HASONEFILTER argument?forum_example_500.pbix (236.3 KB)

First thing and quick fix, look to format your measures better.

Should be more like this

image

Makes a big difference to learning DAX.

Also this is very suboptimal and is making your learning of Power BI much more difficult. Optimizing things here makes a huge difference.

Go through this course when you can.

It should be setup like this.

Think waterfall of filters.

Also though you don’t have a Date table?? You need this.

Maybe the below should just be your date table

image

Regarding the standard deviation calculation.

image

I believe you are correct in your first statement. All you are doing with the ALLEXCEPT statement is remove any filters coming from the Org table EXCEPT for the organization key column.

Regarding the totals, just first think, ‘what is the context of the total’. That is the key.

If you can understand the context, then you can understand what the formula is doing in that context.

If the total is just not right, then yes you can use the HASONEFILTER technique is need be.

See how you go.

Thanks
Sam