Total in Table and Matrix as as calculation of other columns' totals

[Anonymous]

Hi everyone!

I am looking for help in a case that seems easy, but I couldn't find any solution neither in PBI Community nor anywhere in the Internet. I found a lot of similar cases but none of them seems to be what I'm looking for. I wasn't able to create my own solution based on what I found so I decided to ask directly.

I need to visualise simple table where I have WN value for current year (WN 0) and WN value for last year (WN -1). I need to display % change of the value over the course of the year (% change), which I have calculated on the data source as well. All data are avalable for 5 groups (A-E).

I have all 5 rows in the data source (as in the screenshot below) so I basicly just need to display all of them in the Table.

The problem occurs with Total value. Total of '% change' is SUM by default (red -111% below). I need it to be % change of the SUM(WN 0) and SUM(WN -1):

((Total of WN 0) - (Total of WN -1)) / (Total of WN -1)

Like green -47% below.

I have no idea how to modify column in order to recieve correct Total.

I tried to add Measure Column like

WhatINeed = DIVIDE((PBI_Daily_Groups[WN 0]-PBI_Daily_Groups[WN -1]), PBI_Daily_Groups[WN -1])

but that behave exactly like '% change' from the source, or:

WhatINeed = DIVIDE((sum(PBI_Daily_Groups[WN 0])-sum(PBI_Daily_Groups[WN -1])), sum(PBI_Daily_Groups[WN -1]))

And that displays correct value, but duplicates it for all of the rows (and I need original % value for all rows but Total).

I guess it is really easy, but I am definitely missing the point here. Is there any way to achieve my goal?

HARD MODE:

I also have a Matrix that decompose those values, like in example:

Subtotals and Totals behave in the same way as in the first example. Is it possible to achieve my goal for Totals and Subtotals in Matrix?

Best regards!

Michał

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

Hi Ashish,

Thank for your replie. Here is a link to my pbix file:

https://drive.google.com/file/d/19vw_0NhFWBv8jwCPELVNu5K_sEciBKW9/view?usp=sharing

I would be grateful for any help!

Best regards,

Michał

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thank you for your effort Ashish!

However I need to ask... what exactly did you modify? I can't find any differences between your file and my example 😅

Best regards,

Michał

[Ashish_Mathur]

Hi,

I do not remember all the changes that i made but i definitely wrote measures for W0 and W-1.  You had simply dragged those columns.

If my previous reply helped, please mark it as Answer.

[Anonymous]

Of course, I'm kind of blind 😄 now I see and it works just fine! However I feel it should be more intuitive (like in Excel), I am so grateful to you!

[Ashish_Mathur]

You are welcome.