Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
Is there any measure that i can use to calculate the order total when it contains multiple products.
I know that i can use visuals which will do this automatically but i need also a measure to be able to filter orders based on ammounts.
What i need is a measure that will return the total of an order ( AAA= 30 and BBB = 200) so i can then be able to filter these orders based on a given value (more than 50, more than 100, between 10 and 100 etc for which i will use a slicer).
Order | Product | Price |
AAA | nnn | 10 |
AAA | mmm | 20 |
BBB | xxx | 100 |
BBB | yyy | 100 |
Solved! Go to Solution.
this should work:
Measure
#Price = CALCULATE(sum(Sheet1[Price]),ALLEXCEPT(Sheet1,Sheet1[Order]))
Column - which you can use as a slicer
*Price = [#Price]
Thank you for your help, it worked as expected for given example.
However, looking to the data, there are more scenarios that i did not forsee when using the column for slicer (which for me is much more useful).
Is there a way to have a column to return me the Expected result? I do not have other fields to differentiate.
Order | Product | Price | Result | Expected result |
AAA | mmm | 10 | 40 | 40 |
AAA | mmm | 10 | 40 | (blank) |
AAA | nnn | 20 | 40 | (blank) |
BBB | xxx | 100 | 200 | 200 |
BBB | yyy | 100 | 200 | (blank) |
See if this will work for you:
this should work:
Measure
#Price = CALCULATE(sum(Sheet1[Price]),ALLEXCEPT(Sheet1,Sheet1[Order]))
Column - which you can use as a slicer
*Price = [#Price]
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |