Conditional Count

[Pradeepthi23]

Hi I want to calculate the count of orders where the report in code ends with 0,1,2,3,4,5,6,7,8,9 saperately and display the count of orders with respect to the ending values of report in code.

[tamerj1]

@Pradeepthi23 
This with fixed total if it may have any meaninng.

Code Ending Count = 
SUMX ( 
    VALUES ( 'Code Ending'[Code Ending] ),
    CALCULATE (
        SUMX (
            VALUES ( Orders[Report in Code - Display] ),
            VAR CurrentEnding = SELECTEDVALUE ( 'Code Ending'[Code Ending] )
            VAR CurrentCode = Orders[Report in Code - Display]
            VAR EndOfCode = VALUE ( RIGHT ( CurrentCode, 1 ) )
            RETURN 
                IF ( EndOfCode = CurrentEnding, 1 )
        )
    )
)

[Pradeepthi23]

Hi @tamerj1 , Thankyou for the solution, what would be the solution if the ending codes have other values like "A-Z" other than numbers.

[Anonymous]

Hi @Pradeepthi23,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[tamerj1]

@Pradeepthi23 

Would be almost the same

Code Ending Count = 
SUMX ( 
    VALUES ( 'Code Ending'[Code Ending] ),
    CALCULATE (
        SUMX (
            VALUES ( Orders[Report in Code - Display] ),
            VAR CurrentEnding = SELECTEDVALUE ( 'Code Ending'[Code Ending] )
            VAR CurrentCode = Orders[Report in Code - Display]
            VAR EndOfCode =  RIGHT ( CurrentCode, 1 )
            RETURN 
                IF ( EndOfCode = CurrentEnding, 1 )
        )
    )
)

[tamerj1 · Accepted solution]

@Pradeepthi23 
I used a different sample but remains the same code. First create a filter table containg the digits 0 to 9

Create your measure as below and slice by the code ending column from the newly created table.

[tamerj1]

Hi @Pradeepthi23 
Please provide a copy/pase sample of data. Thank you

[Pradeepthi23]

52R8	Z4024223260010
52R8	Z4024384790010
52R9	Z4023432040010
52R9	Z4023992290010
52R9	Z4024116030010
52W0	Z4010402440010
52W0	Z4020015100010
52W0	Z4020022470010
52W0	Z4021628530010
52W1	Z4013535710010