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 sel...
  • 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
johnt75's avatar
johnt75
Icon for Super User rankSuper User
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
  • ritchietodd's avatar
    ritchietodd
    Frequent Visitor
    1 year ago

    Champion! Thanks a lot