Forum Discussion

superBro22's avatar
superBro22
Helper I
4 years ago
Solved

Filter TOPN by dynamic values

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!

 

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

2 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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.