Forum Discussion

LeranPowerBI's avatar
LeranPowerBI
Regular Visitor
9 years ago
Solved

Dynamic Column calculation based on Slicer Selection

I have table "Risk" as below   Month  Amount Jan      100 Feb      200 Mar     878 Apr     547 May    4579 Jun     45 Jul      678 Aug    679 Sep    356 Oct    4568 Nov   3980 Dec   468...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi LeranPowerBI

     

    Try the following

     

    1. Create a table called MonthTable consisting of only MonthNames

         MonthNames

         Jan

         Feb

        .....

        Dec

     

    2. Use this Column MonthName from this MonthTable as a slicer for selecting month.

     

    3. In your data table create a measure called SelectedMonthValue

       SelectedMonthValue =  IF(HASONEFILTER(MonthTable[MonthName]),
                                        LOOKUPVALUE((YourTable[Amount]),YourTable[Month],Values(MonthTable[MonthName]))
                                         ,1)

      What this does is finds the value of Amount from YourTable ( data table) for the selectedmonth in the slicer. If no value is selected in slicer it is set to 1.

     

    4. Now the magic

        Create a measure called Relative in YourTable.

        Relative = SUMX(YourTable,Divide(YourTable[Amount],[SelectedMonthValue]))

        What this does is it iterates YourTable row by row and then calculates the relative value for that row.

     

    Sample screen shot with the data provided by you

     

     

     

    If this solves your issue, please accept it as a solution and also give KUDOS.

     

    Cheers 

     

    CheenuSing

Sean's avatar
Sean
Community Champion
9 years ago

Anonymous

I would only change the last Measure (Step 4) because when nothing is selected in the Slicer those % are basically meaningless :smileyhappy:

 

Relalive % 2 = 
IF (
    HASONEVALUE ( 'Month Table'[Month Name] ),
    DIVIDE ( SUM ( 'Data Table'[Amount] ), [Selected Month Value], 0 ),
    BLANK ()
)

 

 

Nice work! :smileyhappy:

Sean's avatar
Sean
Community Champion
9 years ago

Or instead of blank something like % of Grand Total (when nothing is selected in the Slicer)

 

Relalive % 3 = 
IF (
    HASONEVALUE ( 'Month Table'[Month Name] ),
    DIVIDE ( SUM ( 'Data Table'[Amount] ), [Selected Month Value], 0 ),
    DIVIDE ( SUM ( 'Data Table'[Amount] ), CALCULATE ( SUM('Data Table'[Amount]), ALL('Data Table') ) , 0 )
)

 

Good Luck! :smileyhappy: