topic Re: DAX: category % minus overall % HELP! in DAX Commands and Tips

DAX: category % minus overall % HELP!

ritchietodd — Mon, 03 Feb 2025 09:49:23 GMT

Hi, new here and relatively new to DAX, hoping someone can help.

I have a problem with writing a DAX query that needs to show an overall % for each film's rating.

When an individual genre is selected in PBI, it needs to show the difference between % for each rating within that genre, and the overall %.

For example: across all genres, G rated films form 17.80% of all films.

Within the Action genre, G rated films form 28.13% of all Action films.

I need a measure that will result in showing the difference of 10.33%.

My PBI dashboard:

When Action genre is selected:

I need to put in a third visual that shows 10.33% difference.

I have 3 tables: Films (with all titles, genres and ratings), Genres (distinct list), ratings (distinct list).

all relationships are correct and active.

This is what I have so far:

GenrePercentageMinusTotal =

VAR GenreValue = SELECTEDVALUE(Genres[Genre_Name])

VAR GenrePercent = DIVIDE(CALCULATE(COUNTROWS(Films), Films[Genre] = GenreValue), CALCULATE(COUNTROWS(Films), ALL(Genres)))

VAR TotalPercent = CALCULATE(DIVIDE(COUNTROWS(Films), CALCULATE(COUNTROWS(Films), ALL(Films))), ALL(Films))

RETURN GenrePercent - TotalPercent

Any help would be appreciated; I've been banging my head against the wall for the last few days and am having to admit defeat.

Thanks in advance.

Re: DAX: category % minus overall % HELP!

johnt75 — Mon, 03 Feb 2025 15:51:46 GMT

You can try

Genre percentage minus total = VAR NumFilmsCurrentFilter = COUNTROWS ( Films ) VAR NumFilmsTotal = CALCULATE ( COUNTROWS ( Films ), REMOVEFILTERS () ) VAR NumFilmsInGenre = CALCULATE ( COUNTROWS ( Films ), REMOVEFILTERS ( Ratings ) ) VAR PctTotal = DIVIDE ( NumFilmsCurrentFilter, NumFilmsTotal ) VAR PctGenre = DIVIDE ( NumFilmsCurrentFilter, NumFilmsInGenre ) VAR Result = PctGenre - PctTotal RETURN Result

Re: DAX: category % minus overall % HELP!

ritchietodd — Wed, 05 Feb 2025 15:50:26 GMT

I appreciate the help, but this results in a 26.33% difference:

Re: DAX: category % minus overall % HELP!

johnt75 — Wed, 05 Feb 2025 17:05:29 GMT

Can you share the PBIX file? You can post a link to Google Drive, OneDrive or similar.

Re: DAX: category % minus overall % HELP!

ritchietodd — Wed, 12 Feb 2025 10:43:31 GMT

I think I've managed to share this link OK...

Film Analysis.pbix

Re: DAX: category % minus overall % HELP!

johnt75 — Wed, 12 Feb 2025 16:26:30 GMT

This seems to work

Genre percentage minus total = VAR NumFilmsCurrentFilter = COUNTROWS ( Films ) VAR NumFilmsTotal = CALCULATE ( COUNTROWS ( Films ), REMOVEFILTERS () ) VAR NumFilmsInGenre = CALCULATE ( COUNTROWS ( Films ), REMOVEFILTERS ( Ratings ) ) VAR NumFilmsCurrentRating = CALCULATE( COUNTROWS( Films ), REMOVEFILTERS( Genres ) ) VAR PctTotal = DIVIDE ( NumFilmsCurrentRating, NumFilmsTotal ) VAR PctGenre = DIVIDE ( NumFilmsCurrentFilter, NumFilmsInGenre ) VAR Result = PctGenre - PctTotal RETURN Result

Re: DAX: category % minus overall % HELP!

ritchietodd — Thu, 13 Feb 2025 09:41:06 GMT

Champion! Thanks a lot

Re: DAX: category % minus overall % HELP!

ritchietodd — Thu, 15 May 2025 14:03:10 GMT

Hi again,

I'm trying to accomplish the same thing, but with a difference between average rental amounts and average rental amounts by rating. I've tried to follow a similar process as in your las solution, but I can't quite get my head around it. This is what I have; any assistance would be much appreciated:

Avg Rental Cost minus total =

VAR AvgAmountCurrentFilter =

DIVIDE(SUM(ALL_RENTALS[amount]), COUNTROWS(ALL_RENTALS))

VAR AvgAmountTotal =

CALCULATE(DIVIDE(SUM(ALL_RENTALS[amount]), COUNTROWS(ALL_RENTALS)),REMOVEFILTERS())

VAR AvgAmountInGenre =

CALCULATE(DIVIDE(SUM(ALL_RENTALS[amount]), COUNTROWS(ALL_RENTALS)),REMOVEFILTERS(Ratings))

VAR AvgAmountCurrentRating =

CALCULATE(DIVIDE(SUM(ALL_RENTALS[amount]), COUNTROWS(ALL_RENTALS)),REMOVEFILTERS(Genres))

VAR AmountTotal =

AvgAmountCurrentRating - AvgAmountTotal

VAR AmountGenre =

AvgAmountCurrentFilter - AvgAmountInGenre

VAR Result =

AmountGenre - AmountTotal

RETURN

Result

Cheers (in advance)

Re: DAX: category % minus overall % HELP!

johnt75 — Thu, 15 May 2025 15:42:14 GMT

The calculations for the various average amounts seem to be correct. You can check them by modifying the measure to return each number and check that it gives what you expect.

If they are giving you what you expect then it should just be a matter of picking the right numbers, and the right orders for the subtractions at the end.