Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Measures into Columns - Dynamic Grouping based on window context problem

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 

Need Help

6 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    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