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

 

I want to create a column 'Relativity' based on the month sleection. ie if I choose Month 'Apr' in slicer, all amount value shpuld divide by "Apr" amount 547 in Relativity column.

 

Month  Amount  Relativity
Jan      100            100/547
Feb      200           200/547
Mar     878           848/547
Apr     547            547/547 
May    4579         4579/547
Jun     45                45/547
Jul      678              678/547
Aug    679             679/547
Sep    356                 356/547
Oct    4568          4568/547
Nov   3980           3980/547
Dec   468            468/547

 

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

19 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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:

    • LeranPowerBI's avatar
      LeranPowerBI
      Regular Visitor
      9 years ago

      Thank you very much CheenuSing... it really worked like a magic. :)

    • sieed's avatar
      sieed
      Helper II
      9 years ago

      Anonymous,  I have a date slicer and so the filter is not based on one value (like in this example), rather its a date range (start date, end date). My goal is dynamically cout values based on  the date range selected in the filter. How can I accomplish that? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi sieed

         

        Please share the data and the output desired to work out a possible solution.

         

        Cheers

         

        CheenuSing

  • MattAllington's avatar
    MattAllington
    Community Champion
    9 years ago

    So you need to write measures for this, then put them in a table on a report. 

     

    Month value = sum(risk[amount])

     

    edit: Im not 100% sure (not at my pc) but try this

    relativity = divide([month value],calculate([month value],allselected(risk[month])))

    • LeranPowerBI's avatar
      LeranPowerBI
      Regular Visitor
      9 years ago

      Thanks for your response Matt. but it's giving Relativity as 1 always. I want all amount values to be divided by the amount of selected month.

  • Needler's avatar
    Needler
    New Member
    8 years ago

    I'm trying to do something very similar, but can't get it to work.

     

    I have a table with 2 columns, Dates and Indices. I've got a slicer where the user selects a date:

    In a sepearate table, I've got Projects with an identified Base date:

     

    Based on the value selected in the slicer, I want to create a column in the second table which shows the division between the date selected in the slicer and the date in each row of the table.

     

    Firstly, I created a calculated column to look up the Index based on the base date for each row:

    TPI = RELATED(TPI_Index[London Building Construction Tender Price Index ])

    This works fine.

     

    Then, I tried creating  new measure in the table to show the index selected in the slicer:

    SelectedTPI = IF(HASONEFILTER(TPI_Index[Base Date (qq yyyy)]), LOOKUPVALUE(TPI_Index[London Building Construction Tender Price Index ],TPI_Index[Base Date (qq yyyy)],SELECTEDVALUE(TPI_Index[Base Date (qq yyyy)])) ,0)

     

    The above works fine, but only if the slicer is connected to the table

     

    However, when I do the final step and create the calculation:

    Test_TPI = SUMX('Raw Data',DIVIDE([SelectedTPI],'Raw Data'[TPI]))

     

    I just get a blank column. If I un-link the slicer from the table then SelectedTPI goes to 0, but Test_TPI starts to calculate, but because SelectedTPI is 0 all the cells are reported as 1.

     

    See below for the results, both with the table linked to the slicer filter and without:



     

     

     

     

     

     

     

     

     

     

    Is there a way I can get it to work?

     

    Thanks in advance!