Filter TOPN by dynamic values

[superBro22]

Hi everyone,

Please I  need your help guys. I have 2 doubts:

1) Is there any quick way to filter the TOPN by measuring it by 2 dynamic values. That is, I would like that when applying the TOP N filter, it is calculated by a value A or a value B, which are 2 fields of my table. I would like the user to be able to select with which of the 2 values he wants to measure the TOPN.

2) My second doubt is about grouping: Is there a way to group values from different columns? I have a MATRIX display which has the different measures as values and the months as rows. I would like to be able to make a comparison of different periods, that is: to compare a month with several whole years, quarters , semesters, etc... I want to distribute the different periods in the "Rows" field of my matrix but I can't see how to group these different periods because I have all of them in different columns of my calendar table.

I have it this way:

But I would like to have it this way:

Thanks in advance!

[superBro22]

wonderful proposal! That really solved my issue.

@v-chenwuz-msft  thanks so much for your help!!! 

[v-chenwuz-msft · Accepted solution]

Hi @superBro22 ,

1 You need a slicer for user to select A or B, so create a table for slicer via AorB = {"A","B"}. Then create a measure like the this:

Measure =
VAR _s =
    SELECTEDVALUE ( AorB[Value] )
VAR _A =
    SUM ( 'Table'[ValueA] )
VAR _B =
    SUM ( 'Table'[ValueB] )
RETURN
    IF ( _s = "A", _A, _B )

Result:

2 You can use the date hierarchy. Then use slicer to slicer which month you want.

If you want to keep the total of this year , you can use hasonevalue() to distinguish if it is total row.

all year =
IF (
    HASONEVALUE ( Facttable[Date].[Month] ),
    SUM ( Facttable[values] ),
    CALCULATE (
        SUM ( Facttable[values] ),
        ALLEXCEPT ( Facttable, Facttable[Date].[Year] )
    )
)

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.