DAX: category % minus overall % HELP!

[ritchietodd]

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.

[ritchietodd]

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)

[johnt75]

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.

[johnt75]

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

[ritchietodd]

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

[johnt75]

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

[ritchietodd]

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

Film Analysis.pbix

[johnt75 · Accepted solution]

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

[ritchietodd]

Champion! Thanks a lot