Matrix Measure

[NJ81858]

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!

[Ashish_Mathur]

Hi,

Does it work when you replace HASONEVALUE() with HASONEFILTER()?

[MFelix]

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 )
    )