Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
Please check if you can follow below steps. To make a better presentation, I'm using some data simple.
Suppose the dataset is as
rank = RANKX(Table3,Table3[Company],,,Dense)
Measure = ABS((SUMX(FILTER(Table3,Table3[rank] = MIN(Table3[rank])),Table3[Revenue])-SUMX(FILTER(Table3,Table3[rank] = MAX(Table3[rank])),Table3[Revenue])))
Check more details in the uploaded pbix.
Please check if you can follow below steps. To make a better presentation, I'm using some data simple.
Suppose the dataset is as
rank = RANKX(Table3,Table3[Company],,,Dense)
Measure = ABS((SUMX(FILTER(Table3,Table3[rank] = MIN(Table3[rank])),Table3[Revenue])-SUMX(FILTER(Table3,Table3[rank] = MAX(Table3[rank])),Table3[Revenue])))
Check more details in the uploaded pbix.
Trying to figure out how to display this same information within a matrix. When adding the Measure to Values, we end up getting extra columns added, along with the correct subtotal Measure that we only really want. Any way to turn off those intermediary column values?
hi i tried your solution to my similar problem it worked mostly but one of the variables was a measured column and it didnt work on that...
can you help me understand why would that be and possible solution?
Hi Eric,
Kudos, that's a great solution. I have similar requirement where i will want to select Base Company and compare company in 2 slicers and show +ve/-ve value depending on the slicer 1 or slicer 2. Can you suggest me any thing to solve this?
Thanks
Pramod
Hi Eric,
Kudos, that's a great solution. I have similar requirement where i will want to select Company in 2 slicers and show +ve/-ve value depending on the slicer 1 or slicer 2. Can you suggest me any thing to solve this?
Thanks
Pramod
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.
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..
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |