Forum Discussion
Dynamically calculate differences based on slicer slection
Hi,
I am trying to present the data in a line and clustered column chart,date in the axis, company as column series, and Assets as the column value. In slicer, I have Company (A, B, C....F).
How to create a measure that can dynamically calculate the differences based on user selections in the slicer ( for example A & D). I want to add this difference as the line values, something like the red line in my illustration. Let's assume that user can only select 2 companies to compare at the same time.
| Company | Date | Assets | Expense | Revenue |
| A | 12/1/2015 | 3,257,996 | 213,728,637 | 222,369,686 |
| A | 3/1/2016 | 3,912,477 | 212,545,805 | 221,139,032 |
| A | 6/1/2016 | 4,349,419 | 210,908,531 | 219,435,563 |
| A | 9/1/2016 | 5,066,261 | 208,518,950 | 216,949,371 |
| A | 12/1/2016 | 3,587,758 | 207,589,375 | 215,982,213 |
| B | 12/1/2015 | 14,269,223 | 952,753,212 | 991,273,025 |
| B | 3/1/2016 | 10,885,364 | 954,137,695 | 992,713,482 |
| B | 6/1/2016 | 14,021,908 | 952,367,918 | 990,872,153 |
| B | 9/1/2016 | 14,779,445 | 949,809,780 | 988,210,589 |
| B | 12/1/2016 | 14,661,297 | 947,334,365 | 985,635,093 |
| C | 12/1/2015 | 38,680 | 2,735,227 | 2,845,812 |
| C | 3/1/2016 | 25,787 | 2,746,036 | 2,857,059 |
| C | 6/1/2016 | 38,934 | 2,743,668 | 2,854,595 |
| C | 9/1/2016 | 39,369 | 2,740,828 | 2,851,639 |
| C | 12/1/2016 | 39,523 | 2,737,792 | 2,848,481 |
| D | 3/1/2016 | 4,052,805 | 212,221,695 | 220,801,818 |
| D | 6/1/2016 | 3,907,921 | 211,027,206 | 219,559,036 |
| D | 9/1/2016 | 5,066,261 | 208,639,009 | 217,074,285 |
| D | 12/1/2016 | 3,587,758 | 207,710,835 | 216,108,584 |
| E | 3/1/2016 | 14,877,097 | 950,860,897 | 989,304,203 |
| E | 6/1/2016 | 11,277,965 | 951,824,489 | 990,306,753 |
| E | 9/1/2016 | 14,712,461 | 949,328,434 | 987,709,782 |
| E | 12/1/2016 | 14,592,362 | 946,917,728 | 985,201,612 |
| F | 3/1/2016 | 38,680 | 2,733,143 | 2,843,644 |
| F | 6/1/2016 | 26,040 | 2,743,668 | 2,854,595 |
| F | 9/1/2016 | 39,369 | 2,740,828 | 2,851,639 |
| F | 12/1/2016 | 39,523 | 2,737,792 | 2,848,481 |
Thank you for the help!
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
9 Replies
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.
Want to say the Solution posted worked perfectly!
what value will be displayed if nothing is selected in any of the slicers ? i am trying to achieve the chart where there are 3 columns the first two has impoterd values and third is ratio of rest two respectively. I have put two slicer year and reporting class(the data is sorted for same in the imported file) the problem I am facing is when nothing is selected in slicer it is adding all the ratios and showing instead i want it to do sum of each column respectively and show their ratio.
- 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..
