Forum Discussion
Help for Power BI slicers
- 3 years ago
Check out this article by SQL BI. Although you don't need the TOPN functionality I think that their method for generating a row for Others will work in your situation too.
- 3 years ago
Here is one way.
Create a Dimension table for the slicer values (in my example 'Category Table'), and then another disconnected table to use in the visual following the code along the lines of:
Table Cat with Other = VAR _Other = { ( "Other", 10000000000 ) } VAR _Cats = SUMMARIZE ( FTable, FTable[Category], "Order", RANKX ( ALL ( FTable[Category] ), FTable[Category],, ASC ) ) RETURN UNION ( _Cats, _Other )The measure you need for the visual:
Sum Value = VAR _Sales = CALCULATE ( SUM ( FTable[Value ] ), TREATAS ( VALUES ( 'Table Cat with Other'[Category] ), FTable[Category] ) ) VAR _ALL = CALCULATE ( SUM ( FTable[Value ] ), ALL ( 'Category Table'[Category] ) ) VAR _OtherValue = _ALL - CALCULATE ( SUM ( FTable[Value ] ), ALLSELECTED ( 'Category Table'[Category] ) ) VAR _IfFilt = IF ( ISINSCOPE ( 'Table Cat with Other'[Category] ), IF ( MAX ( 'Table Cat with Other'[Category] ) = "Other", _OtherValue, _Sales ), _ALL ) VAR _NotFilt = IF ( AND ( ISINSCOPE ( 'Table Cat with Other'[Category] ), MAX ( 'Table Cat with Other'[Category] ) = "Other" ), BLANK (), _Sales ) RETURN IF ( ISFILTERED ( 'Category Table'[Category] ), _IfFilt, _NotFilt )Next set up the page with the slicer from the Category Dimension Table, and the visual with the category field from the disconnected table and you will get:
Sample PBIX attached
Here is one way.
Create a Dimension table for the slicer values (in my example 'Category Table'), and then another disconnected table to use in the visual following the code along the lines of:
Table Cat with Other =
VAR _Other = { ( "Other", 10000000000 ) }
VAR _Cats =
SUMMARIZE (
FTable,
FTable[Category],
"Order", RANKX ( ALL ( FTable[Category] ), FTable[Category],, ASC )
)
RETURN
UNION ( _Cats, _Other )
The measure you need for the visual:
Sum Value =
VAR _Sales =
CALCULATE (
SUM ( FTable[Value ] ),
TREATAS ( VALUES ( 'Table Cat with Other'[Category] ), FTable[Category] )
)
VAR _ALL =
CALCULATE ( SUM ( FTable[Value ] ), ALL ( 'Category Table'[Category] ) )
VAR _OtherValue =
_ALL
- CALCULATE ( SUM ( FTable[Value ] ), ALLSELECTED ( 'Category Table'[Category] ) )
VAR _IfFilt =
IF (
ISINSCOPE ( 'Table Cat with Other'[Category] ),
IF ( MAX ( 'Table Cat with Other'[Category] ) = "Other", _OtherValue, _Sales ),
_ALL
)
VAR _NotFilt =
IF (
AND (
ISINSCOPE ( 'Table Cat with Other'[Category] ),
MAX ( 'Table Cat with Other'[Category] ) = "Other"
),
BLANK (),
_Sales
)
RETURN
IF ( ISFILTERED ( 'Category Table'[Category] ), _IfFilt, _NotFilt )
Next set up the page with the slicer from the Category Dimension Table, and the visual with the category field from the disconnected table and you will get:
Sample PBIX attached