Forum Discussion

jbc22's avatar
jbc22
Frequent Visitor
7 years ago
Solved

Slicer to Change Column Donut Chart Utilizes

Hi,

 

I am attempting to develop a report where based on the select in a slicer, the column on which a donut chart is based, changes. 

 

For example, lets say we have a table like the below. Based on a selection in slicer that has option of "Sales" and "Costs", the donut chart will show either the results of Sales as prescribed in the Sales Cat column or the results of Costs as prescribed in the Costs Cat column.

I've attempted a few methods including creating a disconnected table for use in the slicer that will return a measure containing the VALUES() of the column selected, but it does not appear to work. 

 

 

Thanks!

 

,

Need Help
  • AnthonyTilley's avatar
    AnthonyTilley
    7 years ago

    okay slightly diffrent to what was in the orginal.

     

    the way i gave you is to swap the value in the filed but you want to swap the dimention 

     

    For this you need a 3rd table called catagories as below

    Catagories

    5 - 10
    <5
    >10

     

    this will then be joined to both your sales and cost cat colunms (make both joins non active)

     

     

    then use the measure below 

    Measure = 
var sv = SELECTEDVALUE(table2[Type])
var c = CALCULATE(count(Table1[COSTS CAT]),USERELATIONSHIP('Table'[Catagories],Table1[COSTS CAT]))
var s = CALCULATE(count(Table1[SALES CAT]),USERELATIONSHIP('Table'[Catagories],Table1[SALES CAT]))

var ret = SWITCH(sv,
                    "Sales",s,
                    "Costs",c
                    ,0)
Return ret

    simply put this is doing the same as before looking at the selected value in the none joined type table and then will swap between two measures "s" and "c" these two measures will do a count of the costs cat and sales cat Filed and will switch the join based on the relation ship that is needed 

     

    the out come is below 

     

6 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    Hi jbc22 ,

     

    As you refer you need to create a disconeccted table then you should make a measure similar to the one below to use on your donut chart:

    Pie Chart Value =
SWITCH (
    SELECTEDVALUE ( DisconnecteTable[Column] );
    "Sales"; SUM ( Table[Sales] );
    "Cost"; SUM ( Table[Cost] );
    SUM ( Table[Sales] )
)

    Regards,

    MFelix

     

     

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      7 years ago

      Hi jbc22 ,

       

      Replace the SUM by COUNT:

       

      Pie Chart Value =
SWITCH (
    SELECTEDVALUE ( DisconnecteTable[Column] );
    "Sales"; COUNT( Table[Sales] );
    "Cost"; COUNT ( Table[Cost] );
    SUM ( Table[Sales] )
)

      Regards,

      MFelix

      • jbc22's avatar
        jbc22
        Frequent Visitor
        7 years ago

        AnthonyTilley 

         

        Awesome! I edited the format a bit to handle two different legend values for each of the columns in question and it works like a charm! Thanks!

  • AnthonyTilley's avatar
    AnthonyTilley
    Icon for Solution Sage rankSolution Sage
    7 years ago

    Create a second table that is just a list of your headers this will be used for your slicer (no need to join this to anything it is just to be used for a selected value)

    then create a meaure to check the seleced value and return eaither the sum of sales or the sum of costs 

    Measure = 
--get selected value from slicer
var sv = SELECTEDVALUE('Table (2)'[Type])
-- switch between sum of sales and sum of costs based on the value in the selected value, if more than one selected then default to 0
var Ret = SWITCH(sv,
                    "sales", sum('Table'[Sales])
                    ,"Costs", sum('Table'[costs])
                    ,0)
-- return the value
Return Ret

    Example below 

    as you can see in the example above switching the slicer from cost to sales changes the values in the donut chart

    • jbc22's avatar
      jbc22
      Frequent Visitor
      7 years ago

      Hi AnthonyTilley  and MFelix ,

       

      Thanks for the responses. This is close but the expected outcome is a chart that shows a count of the rows within each catagory. So for the data table I provided above, either the left or right chart would be shown based on slicer selection. I don't think this can be acomplished using SUM() in the measure, or am I missing something? Thanks again. 

       

  • AnthonyTilley's avatar
    AnthonyTilley
    Icon for Solution Sage rankSolution Sage
    7 years ago

    okay slightly diffrent to what was in the orginal.

     

    the way i gave you is to swap the value in the filed but you want to swap the dimention 

     

    For this you need a 3rd table called catagories as below

    Catagories

    5 - 10
    <5
    >10

     

    this will then be joined to both your sales and cost cat colunms (make both joins non active)

     

     

    then use the measure below 

    Measure = 
var sv = SELECTEDVALUE(table2[Type])
var c = CALCULATE(count(Table1[COSTS CAT]),USERELATIONSHIP('Table'[Catagories],Table1[COSTS CAT]))
var s = CALCULATE(count(Table1[SALES CAT]),USERELATIONSHIP('Table'[Catagories],Table1[SALES CAT]))

var ret = SWITCH(sv,
                    "Sales",s,
                    "Costs",c
                    ,0)
Return ret

    simply put this is doing the same as before looking at the selected value in the none joined type table and then will swap between two measures "s" and "c" these two measures will do a count of the costs cat and sales cat Filed and will switch the join based on the relation ship that is needed 

     

    the out come is below