Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jbc22
Frequent Visitor

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.

screen.png

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!

 

,

1 ACCEPTED SOLUTION
AnthonyTilley
Solution Sage
Solution Sage

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)

 

joins.png

 

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 

 

donuts.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
AnthonyTilley
Solution Sage
Solution Sage

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)

 

joins.png

 

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 

 

donuts.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




AnthonyTilley
Solution Sage
Solution Sage

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 

s1.png

s2.png

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





Did I answer your question? Mark my post as a solution!

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. 

 screen2.png

MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



jbc22
Frequent Visitor

@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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.