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

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!