cancel
Showing results for
Did you mean:
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

## Re: Dynamic Column calculation based on Slicer Selection

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

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

Proud to be a Datanaut!
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

## Re: Dynamic Column calculation based on Slicer Selection

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

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

Proud to be a Datanaut!
Highlighted
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

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

Nice work!

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

Good Luck!

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

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

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

## Re: Dynamic Column calculation based on Slicer Selection

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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