cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LeranPowerBI
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
CheenuSing
Community Champion
Community Champion

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
Needler
New Member

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:

Grab1.png

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

Grab2.png

 

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:Grab3.png



 

 

 

 

 

 

 

 

 

 

Is there a way I can get it to work?

 

Thanks in advance!

CheenuSing
Community Champion
Community Champion

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!

Hi @CheenuSing 

 

I want to create a dial gauge that will show relative score of multiple fields if I select one record as a baseline.

 

For eg: Let say I have 4 columns -  Region, ProductType, FailureRate and Speed. If I selected Region 1, ProductType A and set them as the base parameter.

 

Now if I select another ProductType, let say B,  the attributes of Product B should be valued relative to the base value selected (ProducType A) and the visualization should be in the form a dial gauge one each for speed and failure rate to compare the performance.

Untitled.png

Could you please help me with this? Thanks in advance.

 

Hi Cheenu,

This is a great post. But I encountered a difficulty that my selectedmonthvalue is alwasy one no matter I switch to other values. Could you please suggest?Capture.PNG

HI @lxiaoxi,

 

The issue is the SelectedMonthValue is a calculated column and not a measure.  Note the calculated columns are computed only once on refresh. They do not get computed for every change in the value of slicers. As  per the formula the value of HASONEFILTER(fieldname) is null and therefore all values are evaluated to If not value 1.

 

If you can elaborate on what you want to actually achieve, then may be I can try to help. If possible place the pbix or data in google drive  or OneDrive and share the link here.

 

Cheers

 

CheenuSing

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

Proud to be a Datanaut!

Hi @CheenuSing

 

Is there any way that the month selected in Slicer will reflects as a value in data table.

 

Regards,

Gaurav More

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.

 

 

CheenuSing
Community Champion
Community Champion

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

 

Capture.GIF

Capture.GIF

 

Replace UserSales with your fact table.

 

If this works for you please accept this 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!

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. 

CheenuSing
Community Champion
Community Champion

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

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

Proud to be a Datanaut!

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

CheenuSing
Community Champion
Community Champion

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!

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

Sean
Community Champion
Community Champion

@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

Sean
Community Champion
Community Champion

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

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.

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.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.