Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
Solved! Go to Solution.
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/
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:
Thank you very much for assistance.
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:
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.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |