Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KY
Regular Visitor

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. 

 

 

CompanyDateAssetsExpenseRevenue
A12/1/2015             3,257,996          213,728,637     222,369,686
A3/1/2016             3,912,477          212,545,805     221,139,032
A6/1/2016             4,349,419          210,908,531     219,435,563
A9/1/2016             5,066,261          208,518,950     216,949,371
A12/1/2016             3,587,758          207,589,375     215,982,213
B12/1/2015           14,269,223          952,753,212     991,273,025
B3/1/2016           10,885,364          954,137,695     992,713,482
B6/1/2016           14,021,908          952,367,918     990,872,153
B9/1/2016           14,779,445          949,809,780     988,210,589
B12/1/2016           14,661,297          947,334,365     985,635,093
C12/1/2015                   38,680               2,735,227          2,845,812
C3/1/2016                   25,787               2,746,036          2,857,059
C6/1/2016                   38,934               2,743,668          2,854,595
C9/1/2016                   39,369               2,740,828          2,851,639
C12/1/2016                   39,523               2,737,792          2,848,481
D3/1/2016             4,052,805          212,221,695     220,801,818
D6/1/2016             3,907,921          211,027,206     219,559,036
D9/1/2016             5,066,261          208,639,009     217,074,285
D12/1/2016             3,587,758          207,710,835     216,108,584
E3/1/2016           14,877,097          950,860,897     989,304,203
E6/1/2016           11,277,965          951,824,489     990,306,753
E9/1/2016           14,712,461          949,328,434     987,709,782
E12/1/2016           14,592,362          946,917,728     985,201,612
F3/1/2016                   38,680               2,733,143          2,843,644
F6/1/2016                   26,040               2,743,668          2,854,595
F9/1/2016                   39,369               2,740,828          2,851,639
F12/1/2016                   39,523               2,737,792          2,848,481

 

 

Capture.JPG

Thank you for the help!

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee

@KY

Please check if you can follow below steps. To make a better presentation, I'm using some data simple.
Suppose the dataset is as

捕获.PNG

 

  1. Create an index column
    rank = RANKX(Table3,Table3[Company],,,Dense)
    捕获.PNG
  2. 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])))
  3. Drag a line and clustered column chart
    233333.png

Check more details in the uploaded pbix.

View solution in original post

9 REPLIES 9
Eric_Zhang
Employee
Employee

@KY

Please check if you can follow below steps. To make a better presentation, I'm using some data simple.
Suppose the dataset is as

捕获.PNG

 

  1. Create an index column
    rank = RANKX(Table3,Table3[Company],,,Dense)
    捕获.PNG
  2. 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])))
  3. Drag a line and clustered column chart
    233333.png

Check more details in the uploaded pbix.

Annotation 2020-06-09 131826.png
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.

Anonymous
Not applicable

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.

 

Company Model.png

 

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]

 

Company Comparison.png

 

 

 

 

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..

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.