Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Guys,
I have the following card and table visuals on my report which are returning an incorrect total.
Eg. Gain/Loss (in EUR) should sum up to 1.5 Million & not -163K.
The formula I have used is
Now I understand this is because Power BI applies the calculation on the total row, the same way as in the row level, any help/guidance on how to correct this is appreciated as I have also tried SUMX but its not working. Thanks for your help in advance.
Thanks
Sangeeta
Solved! Go to Solution.
Hello @sangeetaraman,
Please have a look at the following pictures and I hope the result I obtained is what you are looking for compared to the sample data provided in the excel file.
Excel File Expected Output
PowerBI Output
What you need to do is a SUMX() of the results you obtained over the Origins column.
Here is the link which explains the procedure in detail:
A word of caution:
I have attached my PBIX for your reference and I have changed the name of Plan table to Budget for my reference.
I hope this sufficiently answers your question and if it does please do mark it as the solution and/or kudo it.
Thank you,
Vishesh Jain
Proud to be a Super User!
Hello @sangeetaraman,
Please have a look at the following pictures and I hope the result I obtained is what you are looking for compared to the sample data provided in the excel file.
Excel File Expected Output
PowerBI Output
What you need to do is a SUMX() of the results you obtained over the Origins column.
Here is the link which explains the procedure in detail:
A word of caution:
I have attached my PBIX for your reference and I have changed the name of Plan table to Budget for my reference.
I hope this sufficiently answers your question and if it does please do mark it as the solution and/or kudo it.
Thank you,
Vishesh Jain
Proud to be a Super User!
Hello @visheshjain ,
Thanks very much for your reply and for sharing the link to handle these scenarios.
I ran into some issue while using ISINSCOPE, though ultimately handled it as below:
Glad I could help you out with the problem. All the best!
Proud to be a Super User!
Hi @sangeetaraman ,
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sangeetaraman,
I am not sure about your data model, so I will venture a guess regarding the DAX measure.
You are trying to the sum of some column from the entire column.
I think you should use SUM() for both the columns and then get the difference between them.
Since the columns used in the measure are from different tables, SUMX() will not work (unless you define a virutal table etc. etc., but that is a conversation for another day).
Also, to do a simple difference, you do not need a CALCULATE().
Again, not sure about your data model and how the calcuation should be done, but try tweaking your DAX and see it if works.
Thank you,
Vishesh Jain
Proud to be a Super User!
Hello @visheshjain ,
Thanks for your response.
The first part inside the calculate function is a calculated measure & hence I cannot use a simple SUM() function (i.e. I'm refering to "[Bud cost(with act vol)]" which is a calculated measure).
Gain/Loss (In EUR) = CALCULATE([Bud cost(with act vol)]-SUM(ActualFY23[Actual Total Cost (EUR)]))
The row level calculations done by Power BI are correct ; only the sum total is incorrect as it is considering the totals of each column invidually to calculate the total of Gain/Loss column instead of simply summing it up.
Any help would be most helpful, thanks.
Rgds
Hi @sangeetaraman,
Do you mind sharing a sample file, I'd like to work on it.
Thank you,
Vishesh Jain
Proud to be a Super User!
Hello @visheshjain ,
Thank you so much for your reply. Apologies for my delayed reply, I was preparing some sample data for your better comprehension.
I'm sharing an excel file, sheets "Actual" and "Plan" are the format in which my original data is, except that its several hundred rows really. The common fields are linked in the model view on Power BI.
My requirement is in the 3rd sheet. So I have appended the queries into a new query. There are two calculated measures that I have put in the excel file-which also I have calculated using measure in Power BI. Its returning correct results at a row level, though at a total/aggregated level, the result is completely wrong though I'm using sumx/averagex. I need to use these measures for further calculations/graphs etc. so they have to be measures and not calculated columns.
Could you please help me?
Thank you
Sangeeta