Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Slicers

Hey,

In my report I want to compare the turnover of the selected branches to a benchmark. This benchmark can be selected as well. I do this via two slicers, one for the selected branches and one for the benchmark. Now I want to compare them in a graph but I don't know how to do it.

Because if for example you select branch A and the benchmark is branch B then nothing will be shown as one slicer excludes A and the other excludes B.

Is there a way to work around this without adding an extra table?

I am still quite new to Power BI

 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous 

     

    I reached your expected output with two methods but both of them need extra tables. As you have found, when you select some options in the first slicer, the second slicer will be filtered and the column chart too. So adding a disconnected table that contains values for the second slicer is a solution to solve this. You can add a table easily by DAX codes like:

     

    Benchmarks = VALUES(Table1[Branch])

     

     

    And I created measures to calculate the value for the selected branches and benchmark separately. The result is as below. Please download the .pbix file for more details.

    Selected Branches Total = SUM(Table1[Kolom 1])
    Benchmark Total = SUMX(FILTER(ALL(Table1),Table1[Branch]=SELECTEDVALUE(Benchmarks[Branch])),Table1[Kolom 1])

    Kindly let me know if you have any questions.

    Community Support Team _ Jing Zhang
    If this post helps, please consider Accept it as the solution to help other members find it.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak 

      So I have the following data

      And then I select A and B in a slicer and in a separate slicer I select C as the benchmark. Then I want to output for example the following which I don't know how to do. As the first slicer excludes C from being shown and the second excludes A and B from being shown.

       

      • v-jingzhang's avatar
        v-jingzhang
        Community Support
        5 years ago

        Hi Anonymous 

         

        I reached your expected output with two methods but both of them need extra tables. As you have found, when you select some options in the first slicer, the second slicer will be filtered and the column chart too. So adding a disconnected table that contains values for the second slicer is a solution to solve this. You can add a table easily by DAX codes like:

         

        Benchmarks = VALUES(Table1[Branch])

         

         

        And I created measures to calculate the value for the selected branches and benchmark separately. The result is as below. Please download the .pbix file for more details.

        Selected Branches Total = SUM(Table1[Kolom 1])
        Benchmark Total = SUMX(FILTER(ALL(Table1),Table1[Branch]=SELECTEDVALUE(Benchmarks[Branch])),Table1[Kolom 1])

        Kindly let me know if you have any questions.

        Community Support Team _ Jing Zhang
        If this post helps, please consider Accept it as the solution to help other members find it.