Forum Discussion
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
If this solves your issue, please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
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
I would only change the last Measure (Step 4) because when nothing is selected in the Slicer those % are basically meaningless :smileyhappy:
Relalive % 2 = IF ( HASONEVALUE ( 'Month Table'[Month Name] ), DIVIDE ( SUM ( 'Data Table'[Amount] ), [Selected Month Value], 0 ), BLANK () )Nice work! :smileyhappy:
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! :smileyhappy:
Thank you very much CheenuSing... it really worked like a magic. :)
Anonymous, 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?
Hi sieed
Please share the data and the output desired to work out a possible solution.
Cheers
CheenuSing
Anonymous, here's thed question I posted : https://community.powerbi.com/t5/Desktop/Dynamically-compute-a-calculated-column-based-on-date-slicer/m-p/164093#M71538
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.
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
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.
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?
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
Hi Anonymous
Is there any way that the month selected in Slicer will reflects as a value in data table.
Regards,
Gaurav More
Hi Anonymous
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.
Could you please help me with this? Thanks in advance.
Hi Anonymous i too have a same problem based on the selection of year i need to say whether the project is ongoing or not for that year based on the project closed date.
For example,
project a, has starting date as 12.10.2017 & closing date as 22.12.2024.
Then if i select any year between 2017 to 2024 it must be there befor 2017 it should not be ongoing and after 2024 it must be a closed project.
same if i drill through the next hirerarchy in year slicer that is month it should be there after 10th month 2017 to 12th month 2024.
For the selected year total no of projects must be the count of projects that is ongoing for that particular year.
