Totals in table visual and card visual incorrect

[sangeetaraman]

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 

Gain/Loss (In EUR) = CALCULATE([Bud cost(with act vol)]-SUM(ActualFY23[Actual Total Cost (EUR)]))

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

[visheshjain · Accepted 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:

https://youtu.be/YtIdcCYnZ9w 

A word of caution:

1. I have used only the origins column to sum the visible values of the measure in the total. If you intend to use other columns they will need to be defined in the measure, which is out of scope of this thread.
2. Also, from the sample provided the hierarchy of Origins and Destination is not clear enough as Destinations have multiple origins in both your Acutal and Plan tables.

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

[sangeetaraman]

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:

Sum of Calc Measure = IF(

ISINSCOPE(Location[Origin]), [Calc Measure],

SUMX(addcolumns(summarize(Tablename, Location[Origin]), "New total", [Calc Measure]),

[New total]

))

Thank you again and I hope to keep in touch!

[visheshjain]

Glad I could help you out with the problem. All the best!

[v-jianboli-msft]

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.

[visheshjain]

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

[sangeetaraman]

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

[visheshjain]

Hi @sangeetaraman,

Do you mind sharing a sample file, I'd like to work on it.

Thank you,

Vishesh Jain

[sangeetaraman]

Hello @visheshjain ,

Thank you so much for your reply. Apologies for my delayed reply, I was preparing some sample data for your better comprehension.

https://novelis-my.sharepoint.com/:x:/p/sangeeta_venkataramani/EZOafKHF0AhKtIKpzBJW7o8BCCTOA0DCjVq0J... 

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