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.
Hello community,
I want to dynamically group the top n values into (a) category a = "top n" and (b) all the rest into a category b = "other".
Hence when displaying a bar chart it should always have 2 categories. A filter shoult be there for me to select the top 5, 10, 15, 20 etc.
In many articles our colleges talk about getting the top n products and grouping the rest into the catgory other. The thing is, it always shows the single product names. Instead I would like to have the top n products combined into one category.
https://goodly.co.in/top-n-and-others-power-bi/
Anyone a clou or an article about that topic?
Solved! Go to Solution.
Not sure if this will work but you could try doing it with a calculation group. Create a numeric parameter so that you can dynamically select how many products to include in the top category, and then create calculation items like
Top N =
VAR TopNValue = [Top N Value]
VAR TopNWithValues =
TOPN (
TopNValue,
ADDCOLUMNS (
ALLSELECTED ( 'Product'[Product Key] ),
"@value", SELECTEDMEASURE ()
),
[@value]
)
VAR Result =
SUMX ( TopNWithValues, [@value] )
RETURN
Result
and
Others =
VAR TopNValue = [Top N Value]
VAR TopNWithValues =
TOPN (
TopNValue,
ADDCOLUMNS (
ALLSELECTED ( 'Product'[Product Key] ),
"@value", SELECTEDMEASURE ()
),
[@value]
)
VAR TopNTotal =
SUMX ( TopNWithValues, [@value] )
VAR TotalValue =
CALCULATE ( SELECTEDMEASURE (), ALLSELECTED ( 'Product'[Product Key] ) )
RETURN
TotalValue - TopNTotal
Hope this video helps:
Filter top 3 products with a row for others using DAX in Power BI - YouTube
Not sure if this will work but you could try doing it with a calculation group. Create a numeric parameter so that you can dynamically select how many products to include in the top category, and then create calculation items like
Top N =
VAR TopNValue = [Top N Value]
VAR TopNWithValues =
TOPN (
TopNValue,
ADDCOLUMNS (
ALLSELECTED ( 'Product'[Product Key] ),
"@value", SELECTEDMEASURE ()
),
[@value]
)
VAR Result =
SUMX ( TopNWithValues, [@value] )
RETURN
Result
and
Others =
VAR TopNValue = [Top N Value]
VAR TopNWithValues =
TOPN (
TopNValue,
ADDCOLUMNS (
ALLSELECTED ( 'Product'[Product Key] ),
"@value", SELECTEDMEASURE ()
),
[@value]
)
VAR TopNTotal =
SUMX ( TopNWithValues, [@value] )
VAR TotalValue =
CALCULATE ( SELECTEDMEASURE (), ALLSELECTED ( 'Product'[Product Key] ) )
RETURN
TotalValue - TopNTotal
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |