cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Re: Dynamic Column calculation based on Slicer Selection

Hi CheenuSing,

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

 User Sales U1 120 U2 100 U3 95 U4 110

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.

Highlighted
Super User I

## Re: Dynamic Column calculation based on Slicer Selection

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

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

Proud to be a Datanaut!
Highlighted
Helper I

## Re: Dynamic Column calculation based on Slicer Selection

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.

Highlighted
Super User I

## Re: Dynamic Column calculation based on Slicer Selection

Hi @dk_24

Cheers

CheenuSing

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

Proud to be a Datanaut!
Highlighted
New Member

## Re: Dynamic Column calculation based on Slicer Selection

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?

Highlighted
Frequent Visitor

## Re: Dynamic Column calculation based on Slicer Selection

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?

Highlighted
Super User I

## Re: Dynamic Column calculation based on Slicer Selection

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!
Highlighted
Regular Visitor

## Re: Dynamic Column calculation based on Slicer Selection

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

Regards,

Gaurav More

Highlighted
Regular Visitor

## Re: Dynamic Column calculation based on Slicer Selection

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021