Forum Discussion

George1973's avatar
George1973
Icon for Helper V rankHelper V
4 years ago
Solved

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...
  • VahidDM's avatar
    VahidDM
    4 years ago

    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/

     

     

  • George1973's avatar
    George1973
    4 years ago

    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.

VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

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/

 

 

  • George1973's avatar
    George1973
    Icon for Helper V rankHelper V
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

    • George1973's avatar
      George1973
      Icon for Helper V rankHelper V
      4 years ago

      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.