Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
,
Solved! Go to Solution.
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
Proud to be a Super User!
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
Proud to be a Super User!
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
Proud to be a Super User!
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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
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!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |