calculate percentage by category sum with filters

[lanluokaka]

I have a table1 has ids and table2 has per id, sdk, eventType and library usage data. I'm trying to add a measure in table2 about each eventType usage/total usage in same id,sdk and library. And expecting to use the date and event type filter.

currently I have:

eventTypePercentage = DIVIDE(SUM(table2[usage]),CALCULATE(SUM(table2[usage]),ALLEXCEPT(table2, table2[id],table2[Library],table2[Sdk], table2[appName]), FILTER(ALLSELECTED(table2[DataDate]),table2[DataDate]=table2[DataDate])

event2Percentage = calculate(eventTypePercentage,eventType="2")

The eventTypePercentage works fine. But event2Percentage is not expected. Is there any suggestions about this calculation?

[V-pazhen-msft]

@lanluokaka 

I recreate the formulas in the pbix, basically you can create a total usage column. Then create measures to get the percent, this is more clear and easier to apply in later dax.

totalusagebygroup = CALCULATE(SUM(table2[usage]),ALLEXCEPT(table2, table2[id],table2[Library],table2[Sdk]))
eventpercent = MAX(table2[usage])/MAX(table2[totalusagebygroup])
event2 percent = CALCULATE([eventpercent],FILTER(table2,[eventType]=2))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[lanluokaka]

@V-pazhen-msft Thanks for looking into it. The totalusagebygroup should be a measure. Since I have  a DataDate filter. for example in this case table2 second row should have 100% eventpercent. Since there is only one a, msal and 2.23.3.

[AllisonKennedy]

@lanluokaka Have you tried my suggested solution please? Let me know if it works for you, or if not please provide detail info for which values are wrong and what they should be. 

I have re-attached the file for your reference.

[AllisonKennedy]

@V-pazhen-msft Why have you done the Total Usage by Group as a column? I would prefer to do this as a Measure as it will be more efficient and also less dangerous, as if you SUM that column you'll get inflated usage stats.

[AllisonKennedy]

@lanluokaka  I'm not 100% clear on what you want your percentages based on from your brief description, but this is not working as expected due to DAX Context.

Are you trying to create these as COLUMNS or MEASURES? 

For Event2Percentage did you want to filter both top and bottom of the fraction for Type 2 or just the top?

[lanluokaka]

Thanks for looking into it. I'm trying to create a measure. event percentage is id event usage/(same id,sdk,library with all event usage). for example id a, sdk,msal, 2.33.3 has event 2,3. So event 2 has 100/1000 = 10%. event 3 has 900/1000 = 90%.

Event2Percentage will be separate table based on all the ids in table1.

[AllisonKennedy]

@lanluokaka Depending on how flexible you want this to be, you can code the sdk, library, etc into the DAX, or use them within the visual. What about Dates??

See if the attached is what you're looking for - I've kept it generic and just created a subtotal for ALL event types. Then you can decide within the visual how you want to group them: 

Event Usage = SUM(table2[usage])

Event Usage All Type = CALCULATE([Event Usage], ALL(table2[eventType]))

Event Type Usage % = DIVIDE( [Event Usage], [Event Usage All Type])