Forum Discussion

KY's avatar
KY
Regular Visitor
10 years ago
Solved

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).   ...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    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

     

    1. Create an index column
      rank = RANKX(Table3,Table3[Company],,,Dense)
    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

    Check more details in the uploaded pbix.

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
10 years ago

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

 

  1. Create an index column
    rank = RANKX(Table3,Table3[Company],,,Dense)
  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

Check more details in the uploaded pbix.

  • platocplx's avatar
    platocplx
    New Member
    9 years ago

    Want to say the Solution posted worked perfectly! 

    • Ritu's avatar
      Ritu
      Regular Visitor
      9 years ago

      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's avatar
    Anonymous
    Not applicable
    9 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

  • hyousuf9090's avatar
    hyousuf9090
    Regular Visitor
    6 years ago

    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?

  • geominded1's avatar
    geominded1
    New Member
    6 years ago


    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?