Forum Discussion

ritchietodd's avatar
ritchietodd
Frequent Visitor
1 year ago
Solved

DAX: category % minus overall % HELP!

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's avatar
    johnt75
    1 year ago

    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

8 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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's avatar
      ritchietodd
      Frequent Visitor
      1 year ago

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

       

      • johnt75's avatar
        johnt75
        Super User
        1 year ago

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

  • ritchietodd's avatar
    ritchietodd
    Frequent Visitor
    1 year ago

    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's avatar
      johnt75
      Super User
      1 year ago

      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.