cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dapperscavenger
Helper IV
Helper IV

Dax Percentage formula across multiple columns

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:

 

LocationFiscal Year+Month+NameSKUValue
Country A2019-P08-FebProduct A0
Country A2019-P09-MarProduct A3
Country A2019-P10-AprProduct A22
Country A2019-P11-MayProduct A2
Country A2019-P12-JunProduct A0
Country A2020-P01-JulProduct A0
Country A2020-P02-AugProduct A0
Country A2020-P03-SepProduct A0
Country A2020-P04-OctProduct A0
Country A2020-P05-NovProduct A0
Country A2020-P06-DecProduct A22
Country A2020-P07-JanProduct A13
Country A2020-P08-FebProduct A0
Country A2020-P09-MarProduct A0
Country B2019-P08-FebProduct A0
Country B2019-P09-MarProduct A0
Country B2019-P10-AprProduct A0
Country B2019-P11-MayProduct A0
Country B2019-P12-JunProduct A0
Country B2020-P01-JulProduct A0
Country B2020-P02-AugProduct A0
Country B2020-P03-SepProduct A0
Country B2020-P04-OctProduct A0
Country B2020-P05-NovProduct A0
Country B2020-P06-DecProduct A1
Country B2020-P07-JanProduct A0
Country B2020-P08-FebProduct A0
Country B2020-P09-MarProduct A0
Country B2019-P08-FebProduct B2
Country B2019-P09-MarProduct B4
Country B2019-P10-AprProduct B7
Country B2019-P11-MayProduct B9
Country B2019-P12-JunProduct B0
Country B2020-P01-JulProduct B0
Country B2020-P02-AugProduct B0
Country B2020-P03-SepProduct B0
Country B2020-P04-OctProduct B0
Country B2020-P05-NovProduct B0
Country B2020-P06-DecProduct B4
Country B2020-P07-JanProduct B0
Country B2020-P08-FebProduct B0
Country B2020-P09-MarProduct B0

 

And here are some examples of the results I want to formula to show:

CountryProductMonthNo SKUsNo LocationsNo. MonthsNo. of Values > 0No. of Values = 0% result:
AAALL111451436%
BAALL11141147%
A&BAALL122852318%
BA&BALL212862221%
AA2019-P09-Mar11110100%
AA2019-P08-Feb & 2019-P09-Mar1121150%
BA&B2019-P10-Apr1211050%
A&BA2020-P06-Dec & 2020-P07-Jan1223175%
A&BA&B2020-P06-Dec & 2020-P07-Jan2224267%
BA&B2020-P06-Dec & 2020-P07-Jan2122250%
ALLALLALL2242113126%

 

 

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!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Try a measure like this

divide(sumx(table,if(Table[Value]>0,1,0)),count(Table[Value]]))
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

Try a measure like this

divide(sumx(table,if(Table[Value]>0,1,0)),count(Table[Value]]))
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Amazing, thank you!  

 

Plugged the formula in and suddenly my charts are making a lot more sense 🙂

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors