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
Anonymous
Not applicable

SWAP LEGENDS IN DONUT CHART

Hi All,

 

I have a data which looks like below.

 

ITEM_CD       COUNTRY         SIZE             TYPE
  1                    INDIA              BIG           REGULAR
  2                    INDIA              BIG           REGULAR
  3                    INDIA         MEDIUM       REGULAR
  4                    INDIA         MEDIUM       REGULAR
  5                    USA            MEDIUM        FANCY
  6                    USA            MEDIUM        FANCY
  7                    UK              MEDIUM        FANCY
  8                    UK              MEDIUM        FANCY
  9                    UK                LARGE         FANCY

 

The task i want to perform is to have a donut or pie chart which will have distinct count of ITEM_NUM basis on those 3 columns.

E.G.

When i choose country in filter i will be having 3 countries (INDIA, USA, UK) and disticnt counts of items for respective country.

similarly when i click on type in filter(slicer) i will be having only 2 pie in pie chart (REGULAR, FANCY) and distinct item counts for respective category.

 

I have tried using the same by creating a saperate table which has 3 values (COUNTRY, TYPE, SIZE) and basis on that i tried to change the legends using HASONVALUE or SELECTEDVALUE method.

what i noticed is this method is supporting measures only.

 

Is there a way to include the TEXT data metrics as well in the same method or any other way to do the same .

 

 

Can any one help me out.

 

Thanks in advance

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your decription, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

b1.png

 

You may go to 'Query Editor', make 'COUNTRY', 'SIZE', 'TYPE' selected, click 'Unpivot Columns' in 'Transform' ribbon. In order not to modify the raw data, you may duplicate the query and apply these transformations to it.

b3.png

 

Then you may create a measure as below.

Result = DISTINCTCOUNT('Table'[ITEM_CD])

 

Result:

b4.png

b5.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your decription, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

b1.png

 

You may go to 'Query Editor', make 'COUNTRY', 'SIZE', 'TYPE' selected, click 'Unpivot Columns' in 'Transform' ribbon. In order not to modify the raw data, you may duplicate the query and apply these transformations to it.

b3.png

 

Then you may create a measure as below.

Result = DISTINCTCOUNT('Table'[ITEM_CD])

 

Result:

b4.png

b5.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , Not very clear. seem like you need to use bookmarks

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

Anonymous
Not applicable

Hi @amitchandak ,

 

Basically i just want to have a pie chart, In which i can change legends but not the measure.

E.g.

 

based on the data i have pasted.

If i have a slicer which has values as (COUNTRY, SIZE, TYPE),

and i click on country i should have a pie chart which will show the unique count of items for all the country.

India - 4 items
US - 2 items
UK - 3 items 

 

but once i select type in the slicer

the above pie chart will be changed as follows.

regular- 4 items

fancy - 5 items.

 

 

Hope it is clear now.

 

Thanks

 

 

 

 

Pragati11
Super User
Super User

Hi @Anonymous ,

 

Can you please add details on what you tried and why it is not workin?

 

On existing thread which you can refer for this type of scenario is as follows:

https://community.powerbi.com/t5/Desktop/Dynamic-change-in-X-Axis/m-p/87174

 

This thread shows a solution to dynamically change x-axis using a slicer. Similar concept can be applied to dynamically change legends.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

Hi Pragati11 ,

 

The method i tried is pasted below.

 

FCST_SELECTION_METRICS = 
IF (
HASONEVALUE ( FCST_NAME_TABLE[FCST_NAME] ),
SWITCH (
SELECTEDVALUE ( FCST_NAME_TABLE[FCST_NAME] ),
"ARIMA", SUMX('ACCRY_TEST_05_OCT',[ARIMA_FCST] ),
"ETS", SUMX('ACCRY_TEST_05_OCT',[ETS_FCST_ADJ]),
"LREG", SUMX('ACCRY_TEST_05_OCT',[LREG_FCST] ),
"MAVG", SUMX('ACCRY_TEST_05_OCT',[MAVG_FCST] )
)
)

this method i have written to change the FCST columns in line chart.

here my FCST_NAME_TABLE has 4 rows in FCST_NAME column which have values (ARIMA,ETS,LREG,MAVG).

 

once we click on ARIMA in the slicer the line chart will be changing and will show the trend of ARIMA FCST. and we are summing that up using sumx.

 

Now in the scenarios which i have mentioned i have country,size,type inplace of ARIMA_FCST,ETS_FCST and so on.....

 

FCST_SELECTION_METRICS = 
IF (
HASONEVALUE ( TEST[COLUMN] ),
SWITCH (
SELECTEDVALUE ( TEST[COLUMN] ),
"COUNTRY", ACCRY_TEST_05_OCT[COUNTRY] ,
"SIZE", ACCRY_TEST_05_OCT[SIZE]
)
)

The code i am using is like this, where i m trying to switch text columns in pie chart not the measures.

Which BI is not allowing.

 

The provided soultion can work fine but that demands the data to be duplicated.

 

 

Hope i have explained well.

 

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.