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
George1973
Resolver II
Resolver II

Difference between results based on slicer selection

Hi All,

I have a column with all customers in it and a "Total Sales" measure.
I want to calculate a difference between selected two companies total sales based on a slicer company selection.

- I can not use Selectedvalue mesure, because it only works with one selection

 

Power BI matrix grand totals work like I would like to see.. but vice versa.. I just want not a grand total, but "Grand Difference"..

 

Please advice,

 

Thanks in advance,

2 ACCEPTED SOLUTIONS
VahidDM
Super User
Super User

Hi @George1973 

 

You did not share a sample of data so I will explain my solution, then you can try it:

1- When you select 2 Items with Slicer the your table will be filtered to show all data for those selected customers 
2- You need to write a measure, so first add a VAR to create a table and add a rank column to it to rank rows based on the customers' names (ADDCOLUMN & RANKX), the output will be the filtered table with a column that has 1 and 2 (first customer and 2nd costumer)
3- then add another VAR (Name it S1) to SUMX a sales when the RANK is 1 (Filter RANK =1)

4- add another VAR to SUMX (Name it S2)a sale when the RANK is 2 (Filter RANK =1)

5- at the end (RETURN) S2-S1

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

View solution in original post

Hi,

 

First of all, sorry for beeing late with the answer. I have write a measure according to your suggestion and it works fine.

Variance from Selected Companies = 
VAR summary =
    ADDCOLUMNS (ALLSELECTED(Item_Table[Company]), "cCompany", RANKX(ALLSELECTED(Item_Table[Company]),[Grand Total]))
VAR S1=
    CALCULATE([Grand Total],FILTER(summary,[cCompany]=1))
VAR S2=
    CALCULATE([Grand Total],FILTER(summary,[cCompany]=2))
Return
S1-S2

 

Here is the result:

 

George1973_0-1639563704571.png

Thank you very much for assistance.

View solution in original post

4 REPLIES 4
VahidDM
Super User
Super User

Hi @George1973 

 

You did not share a sample of data so I will explain my solution, then you can try it:

1- When you select 2 Items with Slicer the your table will be filtered to show all data for those selected customers 
2- You need to write a measure, so first add a VAR to create a table and add a rank column to it to rank rows based on the customers' names (ADDCOLUMN & RANKX), the output will be the filtered table with a column that has 1 and 2 (first customer and 2nd costumer)
3- then add another VAR (Name it S1) to SUMX a sales when the RANK is 1 (Filter RANK =1)

4- add another VAR to SUMX (Name it S2)a sale when the RANK is 2 (Filter RANK =1)

5- at the end (RETURN) S2-S1

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Thanks a lot,
I will try to develop a measure as you've suggested and let you know the status.

(Regarding to the issue, that "Why I have not provided" a sample? - It's just en ordinary situation .. Total Sales summirizing the data from one column.. and Companies list from one column.

Hi,

 

First of all, sorry for beeing late with the answer. I have write a measure according to your suggestion and it works fine.

Variance from Selected Companies = 
VAR summary =
    ADDCOLUMNS (ALLSELECTED(Item_Table[Company]), "cCompany", RANKX(ALLSELECTED(Item_Table[Company]),[Grand Total]))
VAR S1=
    CALCULATE([Grand Total],FILTER(summary,[cCompany]=1))
VAR S2=
    CALCULATE([Grand Total],FILTER(summary,[cCompany]=2))
Return
S1-S2

 

Here is the result:

 

George1973_0-1639563704571.png

Thank you very much for assistance.

Hi @George1973 ,

 

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thanks in advance for your kind cooperation!

 

Hope it helps,


Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

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.

Top Solution Authors