cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Dynamic Column calculation based on Slicer Selection

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

 

Averages.GIF

 Averages.GIF

 

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

 

Cheers 

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

18 REPLIES 18
Highlighted

Re: Dynamic Column calculation based on Slicer Selection

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])))



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Regular Visitor

'Re: Dynamic Column calculation based on Slicer Selection

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.

Highlighted
Super User I
Super User I

Re: Dynamic Column calculation based on Slicer Selection

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

 

Averages.GIF

 Averages.GIF

 

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

 

Cheers 

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Dynamic Column calculation based on Slicer Selection

@CheenuSing

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

 

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

 

LOOKUP - Relative %.gif

 

Nice work! Smiley Happy

Highlighted
Community Champion
Community Champion

Re: Dynamic Column calculation based on Slicer Selection

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 )
)

LOOKUP - Relative %3.gif

 

Good Luck! Smiley Happy

Highlighted
Regular Visitor

Re: Dynamic Column calculation based on Slicer Selection

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

Highlighted
Helper II
Helper II

Re: Dynamic Column calculation based on Slicer Selection

@CheenuSing,  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? 

Highlighted
Super User I
Super User I

Re: Dynamic Column calculation based on Slicer Selection

Hi @sieed

 

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

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Highlighted
Helper II
Helper II

Re: Dynamic Column calculation based on Slicer Selection

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors