Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
68 | |
48 | |
45 | |
18 | |
15 |