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 struggling with a specific formula to be implemented in DAX.
My dataset has several dimensions but I have to apply this formula at a certain level of granularity, therefore I have to calculate the formula at "DIM A" and "DIM B" level and then sum up. As per my PowerBI knowledge I should use the SUMX.
The formula is the following:
Measure = IF([Check]<>0,([VOL2]-[VOL1])*[GP/L SC1],[GP2]-[GP1])
Where "check" can be 0 or 1.
Very important is the fact that measure "GP/L SC1" is calculated at "DIM A" level and therefore I defined it in the following way:
GP/L SC1 = CALCULATE(DIVIDE([GP1],[VOL1],BLANK()),ALLEXCEPT(FT,FT[DIM A]))
I then created a measure to perform the SUMX in the following way:
Measure SUMX = SUMX(VALUES('distinct'[DIMA_DIMB]),[Measure])
My problem is that the total value is not actually the sum of the values calculated at DIM A - DIM B level.
Could you please help me?
Of course let me know if my post is not clear.
Fabio
Solved! Go to Solution.
Hi @Anonymous ,
You can update the formula of measure [Measure SUMX] as below, please find the details in the attachment:
Measure SUMX = SUMX(SUMMARIZE('SAMPLE_FT','SAMPLE_FT'[COUNTRY],'SAMPLE_FT'[TECH],"nCheck",[Check]),[Measure])
Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
Ciao @v-yiruan-msft ,
thanks for your reply.
Here is a sample file with sample data. I tried to reproduce as much as possible my data model.
As you can see the total value of the "Measure SUMX" is not the sum of the values at Country-Tech level.
Fabio
Hi @Anonymous ,
You can update the formula of measure [Measure SUMX] as below, please find the details in the attachment:
Measure SUMX = SUMX(SUMMARIZE('SAMPLE_FT','SAMPLE_FT'[COUNTRY],'SAMPLE_FT'[TECH],"nCheck",[Check]),[Measure])
Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
Thank you very much!
I'm including this solution to the real file and let accept it as solution (if it works 😉 ).
Meanwhile thank you again!
Fabio
Hi @Anonymous ,
OK. Looking forward to your reply. Any comment or problem later, please feel free to let me know.
Best Regards
Rena
Hi @Anonymous ,
Whether the other values are correct except the total value? It is a little difficult to find the cause of incorrect total value problem without sample data and applied visual setting in your report. Could you please provide some sample data in your data model, relationship among tables and correct result in order to make troubleshooting?
In addition, you can refer the method in the following links to resolve it.
Fixing Incorrect Totals Using DAX Measures In Power BI
Why Your Total Is Incorrect In Power BI
Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
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 |