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 have a dataset that I'd like to visualize with a dynamic legend - taking the groups with the 7 max individual values in my window context, showing them as they are, then grouping the rest into one bin. I decided to create measures to do so, but as you likely know, measures can't be used as legends. So, I'm stuck trying to convert these window context measures into calculated columns, and I'm lost. Here's the data (Date, Value and Amount) and measures (MaxofDailyValue, RankOfDailyMaxValue, ChartGroup) in a viz table.Ithen slice by other columns, which change the window context. calcs and pbix below.
1.MaxOfDailyValue = CALCULATE(MAXX(SUMMARIZE(Sheet1, Sheet1[Value], Sheet1[Date], "TEst", SUM(Sheet1[Amount])),[TEst]),ALL(Sheet1[Date])) This returns the max of a summarized table (by value and date). IE the max of any given day and value. This is the max value in the window context
2. RankOfDailyMaxValue = RANKX(ALLSELECTED(Sheet1[Value]),[MaxOfDailyValue],,desc,Dense) This ranks the above values in a window context
3. ChartGroup = IF(SELECTEDVALUE(Sheet1[Value]) = BLANK() && [RankOfDailyMaxValue] <= 8,"Group",IF([RankOfDailyMaxValue] <= 8, MAX(Sheet1[Value]), "Group")) I have some unassigned values that are still part of the dataset that should always be grouped in the bulk group, so this does that.
These do exactly what's expected, and changes as I slice other columns (again shown in the pbix). Unfortunately they cannot be used in a legend. Any ideas about translating these into calculated columns?
PBIX: https://1drv.ms/u/s!AuFhcdA3iSEvgdNXrvsBj0I0PUBOAg?e=uY31ZJ
Hi @Anonymous ,
Is there any update?
Best Regards,
Dedmon Dai
Hi @Anonymous ,
If you are calculating topn + others by max of daily value, Please refer to the following steps. First create a new table for x-axis or table visuals:
Table = UNION(VALUES(Sheet1[Value]),ROW("Value","Group"))
Create an inactive relationship between your Sheet1 and the new table .Then you can use the following meaure for topn+others:
Topn + Groups =
VAR N = 1
VAR A =
SELECTEDVALUE ( 'Table'[Value] )
VAR stable =
SUMMARIZE (
Sheet1,
Sheet1[Value],
Sheet1[Date],
"amount", SUM ( Sheet1[Amount] )
)
VAR mtable =
SUMMARIZE (
Sheet1,
Sheet1[Value],
"maxofdailyvalue", MAXX ( FILTER ( stable, Sheet1[Value] = EARLIER ( Sheet1[Value] ) ), [amount] )
)
VAR topntable =
CALCULATETABLE (
VALUES ( Sheet1[Value] ),
TOPN ( N, mtable, [maxofdailyvalue] )
)
RETURN
IF (
A = "Group",
VAR allvalue =
SUMX ( mtable, [maxofdailyvalue] )
VAR topnvalue =
SUMX ( FILTER ( mtable, Sheet1[Value] IN topntable ), [maxofdailyvalue] )
RETURN
allvalue - topnvalue,
CALCULATE (
[MaxOfDailyValue],
FILTER ( 'Table', 'Table'[Value] IN topntable ),
USERELATIONSHIP ( Sheet1[Value], 'Table'[Value] )
)
)
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EQluOjFu69FKhIdbT6h4PDgBPyhrxHEMVA66NYvdKgpkaQ?e=fW9pnW
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@Anonymous , You can not create a calculated column. You need to create and independent table and do segmentation
refer if my viedo or these atricles can help
https://www.youtube.com/watch?v=CuczXPj0N-k
Segmentation
https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
@amitchandak thanks, i've seen videos like this and where I get stuck is on the hardcoding of bins. I need dynamic bins, with the top 7 values in any given window context, not a reference to a table that has set bin values. Is this doable?
@Anonymous , See if this can help: https://community.powerbi.com/t5/Desktop/Top-5-and-others/td-p/165945
I will also try to check
@amitchandak thanks, this link appears to calculate top values via a measure, which is certainly doable but as you know not usable in a legend. You can see my problem!
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 |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |