Percent of multiple subsets over time

[OrthoData]

Greetings, all!
I have a group of categories each with subcategories and totals for all. For example:

Category	Subcategory	Count of Events
1	A	###
1	B	###
1	C	###
2	A	###
2	B	###
2	C	###
3	A	###
3	B	###
3	C	###

I need to graph the percentage of SubCat A for each respective Category over time. All Categories must be represented on a single graph. Right now, I am creating a measure for every %A-of-Category combination but I'm looking for a more efficient and elegant solution. 

I tried doing the % of all A's for total all events, then using the Categories as the Legend, but I then get a rediculously small %.

Thank you all in adance!

[v-kelly-msft · Accepted solution]

Hi @OrthoData ，

You need a measure to calculate % of all A's for total all events as below:

Measure = 
DIVIDE(CALCULATE(SUM('Table'[Count of Events]),ALLEXCEPT('Table','Table'[Subcategory])),CALCULATE(SUM('Table'[Count of Events]),ALL('Table')))

And you will see:

Then you need a measure to calculate the percentage of SubCat A for each respective Category as below :

Measure 2 = DIVIDE(CALCULATE(SUM('Table'[Count of Events]),ALLEXCEPT('Table','Table'[Category]),FILTER('Table','Table'[Subcategory]=SELECTEDVALUE('Table'[Subcategory]))),CALCULATE(SUM('Table'[Count of Events]),ALL('Table')))

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

[OrthoData]

This works. Thank you,

[amitchandak]

Try like

divide(Sum(Table[count of events]),calculate(Sum(Table[count of events]),all(table)))

divide(Sum(Table[count of events]),calculate(Sum(Table[count of events]),allexcept(table[caregory])))

Better than you take category out to a new dimesnion and then do

divide(Sum(Table[count of events]),calculate(Sum(Table[count of events]),all(Category)))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin