Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
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 creaing 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())
Hi @Anonymous ,
According to your requirements, you can directly return the created parameter and observe its return value unit format, because it is possible that sum (parameter) and average (parameter) do not support calculating percentages. The format can be modified in Format. If it is not caused by the above reasons, whether we can provide a detailed data model and expected results, I will answer for you as soon as possible.
Let me know the result immediately, looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-henryk-mstf !
Thank you for your comments.
I have created a new measure called "MyMeasure" and saved it as whole number as follows:
MyMeasure = (CALCULATE(SUM('Data'[Amount]),'Dim1'[Name]="SALES") + CALCULATE(sum('Data'[Amount]), 'Dim1'[Name2]="COST")) / CALCULATE(SUM('Data'[Amount]),'Dim1'[Name]="SALES")
However, now if I try to created a new measure, lets say "NewMeasure" base on "MyMeasure", PowerBI does not allow me to select it. It would like as follows:
NewMeasure = IFERROR((((CALCULATE(SUM(MyMeasure ), FILTER(ALL('Data'[Date]), 'Data'[Date]= Max('Data'[Date]) )))) - AVERAGE(MyMeasure )) / STDEV.S(MyMeasure ), BLANK())
Is it possible what I am trying to do? Or should I try any other way?
Thank you again!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |