Group by and difference in PowerBI

[aadelacr]

I have a column of data which is categorized as year (2023 & 2024). I have created a waterfall chart which showsusage for years 2023 & 2024, the problem is i am trying to create a measure which shows the difference in usage between 2023 & 2024 regardless of the Material NM.

In otherwords, from the Screenshot below, 2024 usage would be $12,497,190 & 2023 would be -$683,971. therefore, the metric i need is $11,813,219. How can i calculate this metric/trend in powerBI? 

[v-weiyan1-msft]

Hi @aadelacr ,

I suggest you to create a Breakdown table.

And please try code as below to create a Calculated table and relate it with your data tables.

Category = 
VALUES('Table'[YEAR])

Then please try code as below to create a measure.

Measure = 
CALCULATE(SUM('Table'[Usage]))

Add the Breakdown column into Breakdown field and the Measure into Y-axis field. Result is as below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aadelacr]

Did you connect the breakdown table somewhere? i am receiving an error

[v-weiyan1-msft]

Thank you very much for your response. I am not having problems when I operate as above. To assist in better pinpointing and resolving these issues, I will attach my .pbix file for your reference. Please try opening it upon receipt and compare it with your file to see if any differences can be found.

Additionally, while calculating the data you provided, I noticed a small discrepancy that I would like to share with you. Regarding the data for the years 2023 and 2024, it seems there may have been a misunderstanding in calculating the difference between the two years.

According to the data you provided:

• The value for 2023 is: -683971
• The value for 2024 is: 12497190

Typically, when seeking the difference between the two, we would calculate the sum of the absolute values of the numbers, rather than adding them directly. Therefore, the difference should be:

∣−683971∣+∣12497190∣=683971+12497190=13181161

I hope this explanation helps to clarify any potential confusion.

Best Regards,

Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.