Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Table 1.
Time | Country | Product | Forecast Quantity | Sales Quantity |
2022 | A | P1 | 10 | 5 |
2022 | A | P2 | 15 | 0 |
2022 | A | P3 | 30 | 100 |
2023 | A | P1 | 5 | 3 |
2023 | A | P2 | 8 | 8 |
2023 | A | P3 | 17 | 27 |
2022 | B | P1 | 1000 | 1050 |
2022 | B | P2 | 5300 | 5710 |
2022 | B | P3 | 156200 | 100540 |
2023 | B | P1 | 1000 | 1050 |
2023 | B | P2 | 5300 | 5710 |
2023 | B | P3 | 156200 | 100540 |
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.
Product | SUM(Forecast Quantity) | SUM(Sales Quantity) | ABS(SUM(Forecast Quantity) - SUM(Sales Quantity) |
P1 | 2015 | 2108 | 93 |
P2 | 10623 | 11428 | 805 |
P3 | 312447 | 201207 | 111240 |
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)) |
325085 | 214743 | 112138 |
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
Solved! Go to Solution.
I think I found the solution myself. What I learned across the way is:
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])
hi @johannesDE I reproduced all the tables you provided, look at the file, maybe it will help you.
I think I found the solution myself. What I learned across the way is:
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])
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |