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!
I'm struggling to create a formula that can give me a % result when filtered by any combination of three other columns.
Here is a mockup of my table:
Location | Fiscal Year+Month+Name | SKU | Value |
Country A | 2019-P08-Feb | Product A | 0 |
Country A | 2019-P09-Mar | Product A | 3 |
Country A | 2019-P10-Apr | Product A | 22 |
Country A | 2019-P11-May | Product A | 2 |
Country A | 2019-P12-Jun | Product A | 0 |
Country A | 2020-P01-Jul | Product A | 0 |
Country A | 2020-P02-Aug | Product A | 0 |
Country A | 2020-P03-Sep | Product A | 0 |
Country A | 2020-P04-Oct | Product A | 0 |
Country A | 2020-P05-Nov | Product A | 0 |
Country A | 2020-P06-Dec | Product A | 22 |
Country A | 2020-P07-Jan | Product A | 13 |
Country A | 2020-P08-Feb | Product A | 0 |
Country A | 2020-P09-Mar | Product A | 0 |
Country B | 2019-P08-Feb | Product A | 0 |
Country B | 2019-P09-Mar | Product A | 0 |
Country B | 2019-P10-Apr | Product A | 0 |
Country B | 2019-P11-May | Product A | 0 |
Country B | 2019-P12-Jun | Product A | 0 |
Country B | 2020-P01-Jul | Product A | 0 |
Country B | 2020-P02-Aug | Product A | 0 |
Country B | 2020-P03-Sep | Product A | 0 |
Country B | 2020-P04-Oct | Product A | 0 |
Country B | 2020-P05-Nov | Product A | 0 |
Country B | 2020-P06-Dec | Product A | 1 |
Country B | 2020-P07-Jan | Product A | 0 |
Country B | 2020-P08-Feb | Product A | 0 |
Country B | 2020-P09-Mar | Product A | 0 |
Country B | 2019-P08-Feb | Product B | 2 |
Country B | 2019-P09-Mar | Product B | 4 |
Country B | 2019-P10-Apr | Product B | 7 |
Country B | 2019-P11-May | Product B | 9 |
Country B | 2019-P12-Jun | Product B | 0 |
Country B | 2020-P01-Jul | Product B | 0 |
Country B | 2020-P02-Aug | Product B | 0 |
Country B | 2020-P03-Sep | Product B | 0 |
Country B | 2020-P04-Oct | Product B | 0 |
Country B | 2020-P05-Nov | Product B | 0 |
Country B | 2020-P06-Dec | Product B | 4 |
Country B | 2020-P07-Jan | Product B | 0 |
Country B | 2020-P08-Feb | Product B | 0 |
Country B | 2020-P09-Mar | Product B | 0 |
And here are some examples of the results I want to formula to show:
Country | Product | Month | No SKUs | No Locations | No. Months | No. of Values > 0 | No. of Values = 0 | % result: |
A | A | ALL | 1 | 1 | 14 | 5 | 14 | 36% |
B | A | ALL | 1 | 1 | 14 | 1 | 14 | 7% |
A&B | A | ALL | 1 | 2 | 28 | 5 | 23 | 18% |
B | A&B | ALL | 2 | 1 | 28 | 6 | 22 | 21% |
A | A | 2019-P09-Mar | 1 | 1 | 1 | 1 | 0 | 100% |
A | A | 2019-P08-Feb & 2019-P09-Mar | 1 | 1 | 2 | 1 | 1 | 50% |
B | A&B | 2019-P10-Apr | 1 | 2 | 1 | 1 | 0 | 50% |
A&B | A | 2020-P06-Dec & 2020-P07-Jan | 1 | 2 | 2 | 3 | 1 | 75% |
A&B | A&B | 2020-P06-Dec & 2020-P07-Jan | 2 | 2 | 2 | 4 | 2 | 67% |
B | A&B | 2020-P06-Dec & 2020-P07-Jan | 2 | 1 | 2 | 2 | 2 | 50% |
ALL | ALL | ALL | 2 | 2 | 42 | 11 | 31 | 26% |
I feel like this should be simple but I've tried a few different things and so far no luck.
Do I need to create multiple formulae e..g one for Location and one for Month, or is it possible to do all in one?
Thank you!
Solved! Go to Solution.
Try a measure like this
divide(sumx(table,if(Table[Value]>0,1,0)),count(Table[Value]]))
Try a measure like this
divide(sumx(table,if(Table[Value]>0,1,0)),count(Table[Value]]))
Amazing, thank you!
Plugged the formula in and suddenly my charts are making a lot more sense 🙂
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |