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.

[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