Forum Discussion
Dynamically calculate differences based on slicer slection
Please check if you can follow below steps. To make a better presentation, I'm using some data simple.
Suppose the dataset is as- Create an index column
rank = RANKX(Table3,Table3[Company],,,Dense)
- Create a measure
Measure = ABS((SUMX(FILTER(Table3,Table3[rank] = MIN(Table3[rank])),Table3[Revenue])-SUMX(FILTER(Table3,Table3[rank] = MAX(Table3[rank])),Table3[Revenue])))
- Drag a line and clustered column chart
Check more details in the uploaded pbix.
- Create an index column
If you like this solution, please visit bipatterns.com which is a new blog I've started for exactly this reason. I'll be adding new posts that go through examples like these each week, hopefully you find it helpful.
I would create two Disconnected tables that have the accounts/companies as a dimension. Add an index column on the table. So now you have two disconnected tables, named Company1 and Company2 for example. Add a third disconected table that has the name of the three measures you want to calculate the difference on (Assets, Expenses, Revenue) and a index column. These are the tables you'll use as the slicer.
Create base measures:
Total Assets:=CALCULATE(SUM(CompanyData[Assets]))
Total Expenses:=CALCULATE(SUM(CompanyData[Expense]))
Total Revenue:=CALCULATE(SUM([Revenue]))
Create Comparison Measure:
SelectedComparisonMeasurePosition:=MIN(Comparison[Position])
SelectedComparisonMeasure:=Switch([SelectedComparisonMeasurePosition],1,[Total Assets],2,[Total Expenses],3,[Total Revenue],BLANK(),[Total Assets])
Create Company measures based on slicer selection:
SelectedCompany1:=MIN([Index])
Company1Name:=LOOKUPVALUE(Company1[Company1],Company1[Index],[SelectedCompany1])
Company1 Selected Measure:=CALCULATE([SelectedComparisonMeasure],FILTER(CompanyData,CompanyData[Company]=[Company1Name]))
Repeat for Company 2:
SelectedCompany2:=MIN(Company2[Index])
Company2Name:=LOOKUPVALUE(Company2[Company2],Company2[Index],[SelectedCompany2])
Company2 Selected Measure:=CALCULATE([SelectedComparisonMeasure],FILTER(CompanyData,CompanyData[Company]=[Company2Name]))
Final Measure to use as your Line value:
Difference Between Selected Companies:=[Company1 Selected Measure]-[Company2 Selected Measure]
Hi rdurkin,
I really like your approach! I was wondering if you can think of an even more general solution?
Let's say, each company has a country field, too.
Is it possible to adjust your solution to be able to calculate the difference in measures no matter if two countries or two compaines were chosen?
I'm looking for a generalized solution to calculate the difference in measures for any two selected attribute values in a model..
