Search code examples
powerbidaxpowerbi-desktopmeasure

PowerBI ZSCORE from a measure

I'm trying to calculate ZSCORE basen on a measure created in DAX.

Measure would be:

Me1 = (CALCULATE(SUM('Data'[Amount]),'Dim1'[Name]="SALES") + CALCULATE(sum('Data'[Amount]), 'Dim1'[Name2]="COST")) / CALCULATE(SUM('Data'[Amount]),'Dim1'[Name]="SALES")

This is giving me the percentages by period.

Now my goal would be to calculate the Zscore of that measure, but I do not know the way to do it from a measure.

I have tried by creating a variable when creating the formula but it says "parameter is not the correct type":

Z-Score_new =
var calc = (CALCULATE(SUM('Data'[Amount]),'Dim1'[Name]="SALES") + CALCULATE(sum('Data'[Amount]), 'Dim1'[Name2]="COST")) / CALCULATE(SUM('Data'[Amount]),'Dim1'[Name]="SALES")
return
IFERROR((((CALCULATE(SUM(calc), FILTER(ALL('Data'[Date]), 'Data'[Date]= Max('Data'[Date]) )))) - AVERAGE(calc)) / STDEV.S(calc), BLANK())

Any idea or any suggestion of how I can get the calculation? Thank you in advance!

Solution

  • I've found a solution by creating a calculate table using DAX:

    Tabla =
SUMMARIZECOLUMNS('TableDim2'[Period],CROSSJOIN('Data','TableDim1','TableDim2','TableDim3'),
"Colum1", IGNORE(CALCULATE(SUM('Data'[Amount]),'Dim1'[Name]="SALES")),
"Column2", IGNORE(CALCULATE(sum('Data'[Amount]),'Dim1'[Name2]="COST")))

    So now I could create a new column such as: 

    FinalMeasure = ((Tabla[Gross1]+Tabla[Gross2])/Tabla[Gross1])

    Which allows me to finally get the Zscore measure:

    Z-Score = IFERROR((((CALCULATE(SUM(Tabla[FinalMeasure]), FILTER(ALL(Tabla[Period]), Tabla[Period]= Max(Tabla[Period]) )))) - AVERAGE(Tabla[FinalMeasure])) / STDEV.S(Tabla[FinalMeasure]), BLANK())