Measures into Columns - Dynamic Grouping based on window context problem

[Anonymous]

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 

[v-deddai1-msft]

Hi @Anonymous ,

Is there any update?

Best Regards,

Dedmon Dai

[v-deddai1-msft]

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

[amitchandak]

@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

[Anonymous]

@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?

[amitchandak]

@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

[Anonymous]

@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!