Forum Discussion
Power BI Dynamic Grouping
I've a requirement on a Table to summarise the data based on below scenarios: The Table 1 is the existing, the Table 2 is what is expecting.
Table 1
| Item# | ItemName | Reason | Catogery | LostSales |
| 28783 | Item 1 | Orders waiting | cat1 | $54 |
| 28783 | Item 1 | Short Term Out Of Stock | cat1 | $13 |
| 28844 | Item 2 | Long Term Out of Stock | cat 2 | $78 |
| 29193 | Item 3 | Orders waiting | cat 3 | $15 |
| 30151 | Item 4 | Discontinue | cat 4 | $302 |
| 30311 | Item 5 | Not applicable | cat 5 | $64 |
| 30663 | Item 6 | Orders waiting | cat 6 | $418 |
Table 2
| Item# | ItemName | Reason | Catogery | LostSales |
| 28783 | Item 1 | Mixed | cat1 | $68 |
| 28844 | Item 2 | Long Term Out of Stock | cat 2 | $78 |
| 29193 | Item 3 | Orders waiting | cat 3 | $15 |
| 30151 | Item 4 | Discontinue | cat 4 | $302 |
| 30311 | Item 5 | Not applicable | cat 5 | $64 |
| 30663 | Item 6 | Orders waiting | cat 6 | $418 |
Any one help on this achieving the Table 2.
Hi,
You need to create measure like this:
Reason_new = var _Reason = LASTNONBLANK(Arkusz1[Reason];1) RETURN IF( CALCULATE( DISTINCTCOUNT(Arkusz1[Reason]) ; all(Arkusz1[Reason])) > 1 ;"Mixed" ;_Reason )This is solution for you.
4 Replies
Hi,
You need to create measure like this:
Reason_new = var _Reason = LASTNONBLANK(Arkusz1[Reason];1) RETURN IF( CALCULATE( DISTINCTCOUNT(Arkusz1[Reason]) ; all(Arkusz1[Reason])) > 1 ;"Mixed" ;_Reason )This is solution for you.
I'm not sure what behaviour you want when filters are applied via slicers that result in only 1 reason being present, but just note that saykasku 's approach will always return "Mixed" regardless of any current filters, while the approach using SELECTEDVALUE() function will switch to showing the single reason if the current filter context eliminates the multiple reasons. Both approaches are valid, it just depends on what your requirements are.
eg
The only way I can think of for doing this would be to create a measure which you would use in place of the "Reason" attribute.
eg.
Reason Text = SELECTEDVALUE( 'Table 1'[Reason], "Mixed")
