Could someone help me on what exactly is the difference between these two measures?
stdev example1:
STDEVX.P (
SUMMARIZE ( ‘Fact’, DimItem[ItemNumber], DimDate[MonthNumber] ),
CALCULATE ( SUM ( ‘Fact’[Revenue] ) ))
stdev example2:
Standard deviation = CALCULATE(STDEV.P(‘NAT Matrix’[Taken Interactions]),ALLEXCEPT(‘NAT Matrix’,‘NAT Matrix’[Year],‘NAT Matrix’[Month],‘NAT Matrix’[ServiceDesk],‘NAT Matrix’[Group]))
Are they returning two different answers or the same?
It difficult to really say, because it can depend on the context of the calculation. With DAX it’s never as easy at just looking at the formula it depends on the relationships you have in your model and also the way you looking to visualize this in your report.
Thanks
Sam
I think I know the difference Example 1 only returns only the total standard deviation, and when I place it within a table produces 0 for the groups that I filtered by, while example 2 takes the individual groups into account. I’m going to make another post with dummy data and post it.
Here: Formulas are below, so is a pic:
stdev_proj_invoice_card = STDEVX.P(SUMMARIZE(DimProject,DimProject[DimProj PROJECT_KEY],"Invoice Values",[Total Invoices]),[Invoice Values])
stdev_proj_invoices = CALCULATE(STDEV.P(FactInvoiceTable[TOTAL_INVOICE_PAID_AMOUNT]),ALLEXCEPT(DimProject,'DimProject'[DimProj PROJECT_KEY]))
Here is the pbix file also:
forum_example_500.pbix (236.3 KB)
@bslyew,
I took a shot at explaining this, so hopefully it helps.
Try not to add columns using summarize, it is better to use ADDCOLUMNS and SUMMARIZE together.
I used variable to make this easier to follow.
EVALUATE
//Gives a table of all the project keys
VAR __SummarizedProjectKeyTable=
SUMMARIZE(
DimProject,
DimProject[DimProj PROJECT_KEY]
)
//Adds the Invoice Column to the summarized table
Var __AddInvoiceTotal=
ADDCOLUMNS(
__SummarizedProjectKeyTable,
"Invoice Values with calculate",
CALCULATE(
SUMX('FactInvoiceTable', 'FactInvoiceTable'[TOTAL_INVOICE_PAID_AMOUNT])
),
"Invoice Values without calculate",
SUMX('FactInvoiceTable', 'FactInvoiceTable'[TOTAL_INVOICE_PAID_AMOUNT])
)
//Add Standard Deviation Columns
Var __StdDevCard=
ADDCOLUMNS(
__AddInvoiceTotal,
"Std Dev using Invoice Values with Calculate",
STDEVX.P(
__AddInvoiceTotal,
[Invoice Values with calculate]
),
"Sted Dev using Invoice Values without Calculate",
STDEVX.P(
__AddInvoiceTotal,
[Invoice Values without calculate]
)
)
RETURN
__StdDevCard order by DimProject[DimProj PROJECT_KEY]
I did this using DaxStudio, but the same theory applies. I attached the query file below if you happen to use DaxStudio (not sure why the code isn’t formatted above), but I go through step-by-step to “See” what is happening.
More or less it just comes down to filter and row context. And how those change in measures.
Your second measure will give you the standard deviation of each project key. You are not summarizing that data, so the column for the Standard Deviation function is the column of Total invoice paid for that specific project key, and then gives you the standard deviation of that.
I hope that makes it somewhat clearer…
Std Dev Example EDNA.dax (955 Bytes)
-Nick
