Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
"I get inconssistent results when using calucalted measures. In the table below
the last 5 coloumns are all calculated measures. Coloumns "CAPEX", "OPEX" and "Days" totals completely incorrect. Below is the same table where I exported this exact table using the "Export Data" function to Excel and totalled the same coloumns:
Any ideas how this can be possible? The individual rows are correct. The totals are wrong.
Paul
Solved! Go to Solution.
Hi @palunel
If the totals are incorrect on your Matrix but correct for each row, then it is quite possible that it is due to the way the calculation is being filtered correctly for each row.
But when you get to the Totals, there is no row context for your measure to calculate the total correctly.
I would suggest reading this blog post by Power Pivot Pro where they do a great job explaining why the values are correct in the Row context, but then appear to be wrong in the Totals. When essentially what you have asked the measure to do, is doing what it should.
They have a solution which I have used multiple times in order to get the correct values based on if it is on a Row or a Total.
Here is their DAX solution, with the blog post link here: Subtotals and Grand Totals That Add Up “Correctly”
[Sales per Day] = IF(COUNTROWS(VALUES(Category[Name]))=1, [Sales], SUMX(VALUES(Category[Name]), [Sales]) )
Hi @palunel
If the totals are incorrect on your Matrix but correct for each row, then it is quite possible that it is due to the way the calculation is being filtered correctly for each row.
But when you get to the Totals, there is no row context for your measure to calculate the total correctly.
I would suggest reading this blog post by Power Pivot Pro where they do a great job explaining why the values are correct in the Row context, but then appear to be wrong in the Totals. When essentially what you have asked the measure to do, is doing what it should.
They have a solution which I have used multiple times in order to get the correct values based on if it is on a Row or a Total.
Here is their DAX solution, with the blog post link here: Subtotals and Grand Totals That Add Up “Correctly”
[Sales per Day] = IF(COUNTROWS(VALUES(Category[Name]))=1, [Sales], SUMX(VALUES(Category[Name]), [Sales]) )
@guavaq wrote:Hi @palunel
If the totals are incorrect on your Matrix but correct for each row, then it is quite possible that it is due to the way the calculation is being filtered correctly for each row.
But when you get to the Totals, there is no row context for your measure to calculate the total correctly.
I would suggest reading this blog post by Power Pivot Pro where they do a great job explaining why the values are correct in the Row context, but then appear to be wrong in the Totals. When essentially what you have asked the measure to do, is doing what it should.
They have a solution which I have used multiple times in order to get the correct values based on if it is on a Row or a Total.
Here is their DAX solution, with the blog post link here: Subtotals and Grand Totals That Add Up “Correctly”
[Sales per Day] = IF(COUNTROWS(VALUES(Category[Name]))=1, [Sales], SUMX(VALUES(Category[Name]), [Sales]) )
Actually after reading some more and implementign this solution it now gives me the correct answers. Thanks.
@guavaq wrote:Hi @palunel
If the totals are incorrect on your Matrix but correct for each row, then it is quite possible that it is due to the way the calculation is being filtered correctly for each row.
But when you get to the Totals, there is no row context for your measure to calculate the total correctly.
I would suggest reading this blog post by Power Pivot Pro where they do a great job explaining why the values are correct in the Row context, but then appear to be wrong in the Totals. When essentially what you have asked the measure to do, is doing what it should.
They have a solution which I have used multiple times in order to get the correct values based on if it is on a Row or a Total.
Here is their DAX solution, with the blog post link here: Subtotals and Grand Totals That Add Up “Correctly”
[Sales per Day] = IF(COUNTROWS(VALUES(Category[Name]))=1, [Sales], SUMX(VALUES(Category[Name]), [Sales]) )
Thanks @GilbertQ but I do not think this is the problem (although the suggestion led to a lot of interesting reading!).
Since both [Ave Occurence] and [Most Likely CAPEX] in the example above are uncalulated coloumns, I changed from using SUMX to using only SUM since I do not require any filtering this should be fine. I get the same incorrect result though.
So I do not really understand where my lack of understanding lies...
Hey Paul, share your measure calculation to understand what is going on
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
So this one totals wrong:
Base CAPEX Cost = SUMX('Base Risk Occurance Data','Base Risk Occurance Data'[Ave Occurence])*SUMX(Base,Base[Most Likely CAPEX])
and this on totals correctly
Base Total Cost = SUMX('Base Risk Occurance Data',[Base CAPEX Cost]+[Base OPEX Cost]+[Base UCLF Cost])
But it is important to note that the individual rows in teh table are actually correct. It is just the totals (and hence if I use a card visualisation) that is wrong.
Paul
Is your [Ave Occurence] an average based measure? Could the average be changing based on the usage on the row by row nature of your matrix?
@Anonymous wrote:Is your [Ave Occurence] an average based measure? Could the average be changing based on the usage on the row by row nature of your matrix?
No, [Ave Occurence] is a non-calculated coloumn resulting from the simmulation that generates the data to be visualised.
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |