DAX/Measure to calculate cumilative count of incidents based on region,country and city

[sowmya2553]

I am trying to achieve below with DAX to get cumilative running total every month as per region, country and city.

In power BI drill down option is enabled and I have to see the cumilative numbers as per the three level hirearchy.

with DAX it seems to work only city level but sometimes it also turns out to be incorrect as per below screenshot.

I badly need this to be worked. Please help

There are few blanks in country column from source file but I cant see any ticket number when I select only blank as filter.

Sample table:

		January	January	February	February	March	March	April	April
		Total Count	Cumil_count	Total Count	Cumil_count	Total Count	Cumil_count	Total Count	Cumil_count
Region	LATAM	5	5	4	9	3	11	2	14
Country	Belgium	2	2	2	4	1	5	1	6
Country	Brazil	3	3	2	5	2	7	1	8
City	SOROCABA	1	1	1	2	1	3	1	4
City	Sau Paulo	2	2	1	3	1	4		4

Tried this and it only gives same count as region for other two down levels,

measure = CALCULATE(DISTINCTCOUNT('SRMS Report'[INCIDENT NUMBER]),

FILTER(ALL('SRMS Report'),

'SRMS Report'[month]<=max('SRMS Report'[month]) && [Country]<=('SRMS Report'[Country]) && [City]<=('SRMS Report'[City]))

)

Main Formula is 

Cumulative_actual = CALCULATE([ticket count],

FILTER(ALL('SRMS Report'),

'SRMS Report'[month]<=max('SRMS Report'[month])),VALUES('Campus Staff Details'[Region]),VALUES('SRMS Report'[Country]),values('SRMS Report'[City])

)

This gives the cumilative count but not accurate

note* ticket count = count(srms report(incident number)

srms report(month) is a month number based on close date column

Please help as this is very much urgent requirment

[v-juanli-msft]

Hi @sowmya2553 

Create a calendar table

calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"year-mn",FORMAT([Date],"yyyymm"))

Create relationships between two tables

Create a measure

Measure =
VAR m1 =
    CALCULATE (
        SUM ( Sheet8[value] ),
        FILTER ( ALLSELECTED ( Sheet8 ), Sheet8[date] <= MAX ( Sheet8[date] ) )
    )
VAR m2 =
    CALCULATE (
        SUM ( Sheet8[value] ),
        FILTER (
            ALLSELECTED ( Sheet8 ),
            Sheet8[region] = MAX ( Sheet8[region] )
                && Sheet8[country] = MAX ( Sheet8[country] )
                && Sheet8[date] <= MAX ( Sheet8[date] )
        )
    )
VAR m3 =
    CALCULATE (
        SUM ( Sheet8[value] ),
        FILTER (
            ALLSELECTED ( Sheet8 ),
            Sheet8[region] = MAX ( Sheet8[region] )
                && Sheet8[country] = MAX ( Sheet8[country] )
                && Sheet8[city] = MAX ( Sheet8[city] )
                && Sheet8[date] <= MAX ( Sheet8[date] )
        )
    )
RETURN
    IF (
        ISINSCOPE ( Sheet8[region] ),
        IF (
            ISINSCOPE ( Sheet8[country] ),
            IF ( ISINSCOPE ( Sheet8[city] ), m3, m2 ),
            m1
        ),
        0
    )

If it doesn't meet your requirement, please let me know.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sowmya2553]

Hello Maggie,

Thanks for your reply and help.

I have created a calendar table and gave a relation and tried to create a measure then.

it gives below error:

The column 'Campus Staff Details [Region]' either doesn't exist or doesn't have any relationship to any table in current context.

(Please note: I have already build a relation between SRMS report table and Campus Staff details table with key column COUNTRY)

This is my DAX which I modified

Measure_DAX =

VAR m1 =

CALCULATE (

        SUM ( 'SRMS Report'[INCIDENT NUMBER]),

        FILTER ( ALLSELECTED ( 'SRMS Report'), 'SRMS Report'[COMPLETION DATE TIME] <= MAX ('SRMS Report'[COMPLETION DATE TIME]) )

    )

VAR m2 =

    CALCULATE (

        SUM ( 'SRMS Report'[INCIDENT NUMBER]  ),

        FILTER (

            ALLSELECTED ('SRMS Report' ),

            RELATED('Campus Staff Details'[Region] )= MAX ( 'Campus Staff Details'[Region] )

                && 'SRMS Report'[Country] = MAX ( 'SRMS Report'[Country] )

                && 'SRMS Report'[COMPLETION DATE TIME] <= MAX ('SRMS Report'[COMPLETION DATE TIME] )

        )

    )

VAR m3 =

    CALCULATE (

        SUM ('SRMS Report'[INCIDENT NUMBER] ),

        FILTER (

            ALLSELECTED ( 'SRMS Report' ),

            RELATED('Campus Staff Details'[Region]) = MAX ( 'Campus Staff Details'[Region] )

                && 'SRMS Report'[Country] = MAX ( 'SRMS Report'[Country])

                && 'SRMS Report'[City] = MAX ( 'SRMS Report'[City])

                && 'SRMS Report'[COMPLETION DATE TIME] <= MAX ( 'SRMS Report'[COMPLETION DATE TIME] )

        )

    )

RETURN

    IF (

        ISINSCOPE ('Campus Staff Details'[Region]),

        IF (

            ISINSCOPE ( 'SRMS Report'[Country] ),

            IF ( ISINSCOPE ( 'SRMS Report'[City]), m3, m2 ),

            m1

        ),

        0

    )

I guess the issue is, Region is coming from different table hence I used related but not sure if thats the issue..

Relationship is given to DATE column from calendar table to COMPLETEION DATE TIME field in SRMS table.