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.
I am trying to add a new measure that can calulate cumulative total of value of an item and summarise it on based on probablity.
This is the data that I have
Item | Value | Probablity |
4 | 64 | 0.9 |
4 | 76 | 0.8 |
2 | 42 | 0.0 |
1 | 93 | 0.9 |
1 | 84 | 0.8 |
2 | 85 | 0.1 |
1 | 99 | 0.2 |
1 | 55 | 0.1 |
2 | 91 | 1.0 |
2 | 47 | 0.5 |
2 | 71 | 0.7 |
3 | 80 | 0.4 |
So if I summarise by probablity 0.8 and item 1, it should give me the sum of value for all probablity greater than 0.8
This is the report that I want to create.
Item | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 | 1 |
1 | 331 | 276 | 177 | 177 | 177 | 177 | 177 | 177 | 93 | 0 |
2 | 294 | 209 | 209 | 209 | 209 | 162 | 162 | 91 | 91 | 91 |
3 | 80 | 80 | 80 | 80 | 0 | 0 | 0 | 0 | 0 | 0 |
4 | 140 | 140 | 140 | 140 | 140 | 140 | 140 | 140 | 64 | 0 |
Can anyone help me out.
Solved! Go to Solution.
hi friend
First, Create a new Table (Modeling Enter Data)
2. Related Both Table
3. Create a New Measure
SumValues = CALCULATE ( SUM ( Table1[Value] ), FILTER ( ALLEXCEPT ( Table1; Table1[Item] ), Table1[Probablity] >= MAX ( Probabilities[Probability] ) ) ) + 0
4. Ready. You can view in a Matrix
hi friend
First, Create a new Table (Modeling Enter Data)
2. Related Both Table
3. Create a New Measure
SumValues = CALCULATE ( SUM ( Table1[Value] ), FILTER ( ALLEXCEPT ( Table1; Table1[Item] ), Table1[Probablity] >= MAX ( Probabilities[Probability] ) ) ) + 0
4. Ready. You can view in a Matrix
I tried but it is not working for me. I must be missing something.
This is the measure that I have:
SumValue = CALCULATE( SUM('Item'[Value]), FILTER( ALLEXCEPT('Item','Item'[Item]), 'Item'[Probability] <= MAX(Probablities[Probability]) ) ) + 0
And this the matrix that I get:
What am I missing?
It should be "greater than" (>=) the custom porbability in filter context:
SumValue = CALCULATE( SUM('Item'[Value]), FILTER( ALLEXCEPT('Item','Item'[Item]), 'Item'[Probability] >= MAX(Probablities[Probability]) ) ) + 0
Regards,
Sorry. my mistake. I tried changing the sign and doing again but it wasn't working.
Then I created everything from the scratch again and now it is working.
Thank you so much!
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |