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.
Hi,
I have two measures (Price & Volume) that both do a sum on the same table and same columns but based on different filters on that specific table. I also have a measure (EURTotal) that multiplies these two measures. Currently it also multiplies the totals, however, I want it to SUM per period (YearMonth). These periods come from a separate Calendar table, linked to the table 'FACT'.
The current (simplified) dataset is used:
Sample data, Table 'FACT'
Date | Subject | Quantity_Type | Quantity |
2020-01-01 | TB | Volume | 10 |
2020-02-01 | TB | Volume | 200 |
2020-03-01 | TB | Volume | 30 |
2020-04-01 | TB | Volume | 70 |
2020-01-01 | TB | Price | 2 |
2020-02-01 | TB | Price | 5 |
2020-03-01 | TB | Price | 4 |
2020-04-01 | TB | Price | 3 |
Measure 'Price':
Price =
CALCULATE(
SUM('FACT'[Quantity]),
FILTER('FACT', 'FACT'[Subject] = "TB"),
FILTER('FACT', 'FACT'[Quantity_Type] = "Price")
)
And 'Volume':
Volume =
CALCULATE(
SUM('FACT'[Quantity]),
FILTER('FACT', 'FACT'[Subject] = "TB"),
FILTER('FACT', 'FACT'[Quantity_Type] = "Volume")
)
By simply multiplying I get, obviously, not a SUM in the total column, but a PRODUCT of the two SUMS. As you can see here:
Current result:
Date | Volume | Price | Vol*Price |
2020-01-01 | 10 | 2 | 20 |
2020-02-01 | 200 | 5 | 1000 |
2020-03-01 | 30 | 4 | 120 |
2020-04-01 | 70 | 3 | 210 |
Total | 310 | 14 | 4340.00 |
What I tried is to use SUMX, but somehow multiplying SUMX * SUMX it gives the exact same result as SUM * SUM.
Expected result:
Date | Volume | Price | EURTotal |
2020-01-01 | 10 | 2 | 20 |
2020-02-01 | 200 | 5 | 1000 |
2020-03-01 | 30 | 4 | 120 |
2020-04-01 | 70 | 3 | 210 |
Total | 310 | 14 | 1350.00 |
What I specifically tried is the following:
EURTotal =
CALCULATE(
SUMX('FACT'[Quantity]),
FILTER('FACT', 'FACT'[Subject] = "TB"),
FILTER('FACT', 'FACT'[Quantity_Type] = "Price")
)
*
CALCULATE(
SUMX('FACT'[Quantity]),
FILTER('FACT', 'FACT'[Subject] = "TB"),
FILTER('FACT', 'FACT'[Quantity_Type] = "Volume")
)
This doesn't work, but I don't see how I could get it in a working SUMX format like the following, for instance:
EURTotal = SUMX (FACT,
FACT[Quantity] --but then a filter here on Price
* FACT[Quantity] --but then a filter here on Volume
)
which I found here: https://www.burningsuit.co.uk/blog/2018/07/dax-is-the-total-row-in-a-table-giving-you-grief/
Any suggestions?
Kind regards,
Igor
Solved! Go to Solution.
Hi @Anonymous ,
Have you tried using "pivot column" in the query editor?
Then try this measure:
Measure = CALCULATE(SUMX(VALUES('Table'[Date]),CALCULATE(SUM('Table'[Price])*SUM('Table'[Volume]))))
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Have you tried using "pivot column" in the query editor?
Then try this measure:
Measure = CALCULATE(SUMX(VALUES('Table'[Date]),CALCULATE(SUM('Table'[Price])*SUM('Table'[Volume]))))
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Try measure like
sumx(summarize(Table, Table[date], table[Subject], "_1" ,[price] , "_2",[volume]),[_1]*[_2])
Thanks for the quick response!
Unfortunately, I get the exact same result. I used this measure to try it out:
EURTotal =
SUMX(
SUMMARIZE('FACT',
'DIM Kalender'[Datum], --same result as 'FACT'[Date]
'FACT'[Subject],
"_1", [Volume],
"_2", [Price]),
[_1]*[_2])
So the Total still has the value of the multiplication of the Total Volume * Total Price.
Kind regards,
Igor
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |