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

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

dk_24's avatar
dk_24
Helper I
8 years ago

Hi CheenuSing,

 

i am also looking for similar kind of issue , can you please help out to find the solution .

 

User Sales
U1120
U2100
U395
U4110

     

I Have a slicer with User , Suppose if i select U2 , U2 sales is 100 now i want to compare with the other users 

Ex = Slicer selection U2

100 - (120+95+110)/3 

the difference with the remaing users are -8  .

suppose if i select U4 in Slicer

the output should be 110 - (120+100+95) /3 = -5 

 

Thanks,

DK.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi dk_24

     

    Here you go.

     

    1.  Create a DimUsers table as

        DimUsers = Summarize(UserSales,[User])

        This table will be used for slicers. This should not be linked to the fact table.

     

    2. Create a measure 

        SelectedUserValue = IF(HASONEFILTER(DimUsers[User]),CAlculate(sum([Sales]),FIlter(USerSales,[User]=VALUEs(DimUsers[User]) ) ) ,blank())

         This gives the selected users sales value.

     

    3. Create a measure

    OtherUsersSales = IF(HASONEFILTER(DimUsers[User]),CAlculate(sum([Sales]),FIlter(USerSales,[User]<>VALUEs(DimUsers[User]) ) ) ,blank())

    This gives the remaining user sales values.

     

    4.  Create a measure

    UserCount = DistinctCount([User])

    This gives the total user count in the fact table.

     

    5. Finally create a measure Difference as

       Difference = If (NOT ISBLANK([SelectedUserValue]), [SelectedUserValue] - Divide([OtherUsersSales],([UserCount] - 1)))

     

    6.  Sample screen shot

     

     

    Replace UserSales with your fact table.

     

    If this works for you please accept this as a solution and also give KUDOS.

     

    Cheers

     

    CheenuSing

     

     

     

    • dk_24's avatar
      dk_24
      Helper I
      8 years ago

      Thanks CheenuSing for response.

      i have doubt on point 1 .

      1.  Create a DimUsers table as

          DimUsers = Summarize(UserSales,[User])

          This table will be used for slicers. This should not be linked to the fact table

       

      i have a few more visulizations in same page , i have to use same slicer for all visulizations on that page.if should nt linked to the fact table, will same slicer will work for all visulzations of page.

       

      thanks,

      dk. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi dk_24

         

        It is difficult to answer unless you share the pbix.  Please post a  link here to dwonload with sensitive data masked.

         

         

        Cheers

         

        CheenuSing