Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi, @Anonymous
Based on your decription, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
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.
Then you may create a measure as below.
Result = DISTINCTCOUNT('Table'[ITEM_CD])
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your decription, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
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.
Then you may create a measure as below.
Result = DISTINCTCOUNT('Table'[ITEM_CD])
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Not very clear. seem like you need to use bookmarks
https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |