Forum Discussion

NJ81858's avatar
NJ81858
Helper IV
3 years ago

Matrix Measure

Hello,

 

I have a matrix that has Teams as the first level of the matrix, and then the number of times they asked a customer about a promotion, with the values being a count of transaction, broken into "Yes" and "No", and then a percentage, where the percentages within each team are the percent of the total responses for the team, and then the total percentage is the total transactions per team divided by the overall total of transactions. 

The issue with this matrix is that the 5th team isn't calculating correctly for the percentages. My measure to calculate the percentage is:

 

Percent Measure = 

VAR _each = CALCULATE ( COUNT ( 'Table'[Transaction ID]), FILTER('Table', [Promotion?] = [Promotion]))
VAR _total = CALCULATE ( COUNT ( 'Table'[Transaction ID]), ALL('Table'[Promotion?]))
VAR _overalltotal = CALCULATE ( COUNT ( 'Table'[Transaction ID]), ALLEXCEPT('Table', 'Table'[Date], 'Table'[Transaction Status]) )

RETURN
IF ( HASONEVALUE('Table'[Promotion?]), DIVIDE(_each, _total), DIVIDE(_total, _overalltotal))

 

My measure works perfectly normal for all values except for that one team and I am not sure where I am going wrong. Thank you in advance!

 

Need Help

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    Hi NJ81858,

     

    You issue is with the formula for the HASONEVALUE part, since team 5 only has one promotion and it's values is yes instead of picking up the 0 it's picking up 1/122 then the result is the same for the line of the no and for the line of the team 5, try the following code:

     

     

    Percent measure = 
VAR _each =
    CALCULATE (
        COUNT ( 'Table'[Transaction ID] ),
        FILTER ( 'Table', [Promotion?] = [Promotion] )
    )
VAR _total =
    CALCULATE ( COUNT ( 'Table'[Transaction ID] ), ALL ( 'Table'[Promotion?] ) )
VAR _overalltotal =
    CALCULATE (
        COUNT ( 'Table'[Transaction ID] ),
        ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Transaction Status] )
    )
RETURN
    IF (
        ISFILTERED( 'Table'[Promotion?] ),
        DIVIDE ( _each, _total ),
        DIVIDE ( _total, _overalltotal )
    )