Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I have a report that I need the end user to be able to categorise a 'holding' on my 'holdings table' as either beef or dairy based on a percent using a visual filter, eg slicer. The filter is based of a percentage, ie, if the holding has 'PERCENT_DAIRY'>= x then categorise these holdings as 'Dairy', else categorise as beef
Below is the sample data:
HOLDINGID PERCENT_DAIRY
Holding_1 60
Holding_2 80
Holding_3 40
Holding_4 20
Holding_5 90
Holding_6 94
If the end user uses a filter, eg slicer to say that any holding with 'PERCENT_DAIRY' IS >= 80 then 'dairy' else 'beef'
END RESULT:
HOLDINGID PERCENT_DAIRY CATEGORY
Holding_1 60 BEEF
Holding_2 80 DAIRY
Holding_3 40 BEEF
Holding_4 20 BEEF
Holding_5 90 DAIRY
Holding_6 94 DAIRY
I have the 'holding' table with holding data (see above) and a 'percent' table showing values 1 - 100
My question is how do I create a custom category (ie, dairy, beef) based on the percent choosen by the end user
Thank you for any help you can provide.
Solved! Go to Solution.
Hi @justinMcC,
Create 2 Measures to achieve this ac below.
Measure 1:
Calculate Dairy % = CALCULATE(SUMX(your_table_name, your_table_name[PERCENT_DAIRY]),ALLEXCEPT(your_table_name, your_table_name[HOLDINGID]))
Measure 2:
Category = IF([Calculate Dairy %] >= 80, "DAIRY","BEEF")
Please give a kudo and Accept the soultion if it works for you.
Thanks,
Mehbub- India
Hi @justinMcC,
Create 2 Measures to achieve this ac below.
Measure 1:
Calculate Dairy % = CALCULATE(SUMX(your_table_name, your_table_name[PERCENT_DAIRY]),ALLEXCEPT(your_table_name, your_table_name[HOLDINGID]))
Measure 2:
Category = IF([Calculate Dairy %] >= 80, "DAIRY","BEEF")
Please give a kudo and Accept the soultion if it works for you.
Thanks,
Mehbub- India
Hi @mehaboob557
Thanks for all your help, your solution worked
The next step I have however is to create a chart from the results, ie Beef = 4, Dairy = 4, however I cant use a measure is a chart
Do you know how to turn a measure into a column?
Again thank you for the above, your help was much appreciated
Show me some output how you need.
If possible create a new forum post and send me the link . I will check and if possible i will help
Hi @mehaboob557
I created another topic on forum, here is the link:
http://community.powerbi.com/t5/Desktop/Turning-a-column-into-a-measure/m-p/331989#M148440
Any help you could give would be much appreciated
Thanks
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |