Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
johannesDE
Frequent Visitor

Dynamically calculate the difference of two column sums (grouped by another column)

Dear PBI community,

I am looking for a way to create the sums of two columns based on applied filters by a group (i.e. "Product") and then calculating the absolute difference.

The table below represents the source data. Users should be able to filter on a subset of:

  • Time
  • Country
  • Product

Table 1.

TimeCountryProductForecast QuantitySales Quantity
2022AP1105
2022AP2150
2022AP330100
2023AP153
2023AP288
2023AP31727
2022BP110001050
2022BP253005710
2022BP3156200100540
2023BP110001050
2023BP253005710
2023BP3156200100540

Then, based on the selection, I would like to aggregate the source data for the selected columns by column "Product" and calculate the absolute difference of these sums:

Table 2.

ProductSUM(Forecast Quantity)SUM(Sales Quantity)ABS(SUM(Forecast Quantity) - SUM(Sales Quantity)
P12015210893
P21062311428805
P3312447201207111240

Last, I would like to sum all the columns in Table 2 and calculate a measure as follows:

Table 3.

COLUMNSUM(SUM(Forecast Quantity))COLUMNSUM(SUM(Sales Quantity))COLUMNSUM(ABS(SUM(Forecast Quantity) - SUM(Sales Quantity))
325085214743112138

 

Variable = 1 - [COLUMNSUM(ABS(SUM(Forecast Quantity) - SUM(Sales Quantity))] / [COLUMNSUM(SUM(Forecast Quantity))]

With the numbers provided, I would get:

Variable = 1 - 112138/325085 = 65.5%

I attempted to create a calculated table with the sums. However, I was not able to compute the sum of differences by a group ("Product") and have it dynamically adjusted based on filters the end user is placing by selecting a subset of countries or time periods.

Using the R script visual, it is possible for me to perfom the aggregations, however the underlying data is too large (>1M rows) to pass to the R visual which appears to be restricted to 150k rows to my knowledge.

I am grateful for your help with this question! Sitting on it for a few hours already 🙂

Best wishes,

Johannes

1 ACCEPTED SOLUTION
johannesDE
Frequent Visitor

I think I found the solution myself. What I learned across the way is:

  1. calculated tables are (as of today) not dynamically recalculated based on slicer settings
  2. measures are dynamically recalulated from slicers (no need to explicitly code this in)

Then, the two simple DAX measures do the job. First, I calculate the 'sum of differences' as

 

Differences = SUMX(
     VALUES('Table'[Product]),
     CALCULATE(
         ABS(SUM('Table'[Forecast Quantity]) - SUM('Table'[Sales Quantity]))))

 

From my understanding, SUMX() allows for row-wise summation by groups. The grouping is done via VALUES().

Next, I calculate the 'Variable' as follows

 

Variable = 1 - [Differences]/SUM('Table'[Forecast Quantity])

 

 

View solution in original post

2 REPLIES 2
DimaMD
Solution Sage
Solution Sage

hi @johannesDE  I reproduced all the tables you provided, look at the file, maybe it will help you.


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
johannesDE
Frequent Visitor

I think I found the solution myself. What I learned across the way is:

  1. calculated tables are (as of today) not dynamically recalculated based on slicer settings
  2. measures are dynamically recalulated from slicers (no need to explicitly code this in)

Then, the two simple DAX measures do the job. First, I calculate the 'sum of differences' as

 

Differences = SUMX(
     VALUES('Table'[Product]),
     CALCULATE(
         ABS(SUM('Table'[Forecast Quantity]) - SUM('Table'[Sales Quantity]))))

 

From my understanding, SUMX() allows for row-wise summation by groups. The grouping is done via VALUES().

Next, I calculate the 'Variable' as follows

 

Variable = 1 - [Differences]/SUM('Table'[Forecast Quantity])

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors