Search code examples
powerbidax

Running Total DAX by Region

enter image description here

I have got measure created Running Total as displayed in the picture. One measure is by month. It is slicing by month. I have got dimension called Region. I want to slice it by region instead of month. dimRegion have got one to many relationship with Fact table. But When I place region instead of month it shows Running Total value same instead of showing the running total as it is showing when I slice by Month

YTD :=
VAR Year =
    ( MAX ( 'Target'[Year] ) )
VAR MaxMonthNumber =
    CALCULATE (
        MAX ( 'fact'[MonthNumber] ),
        'fact'[Year] = Year
    )
VAR MinMonthNumber =
    MIN ( 'dimMonthNames'[MonthNumber] )
VAR ShowData = ( MinMonthNumber <= MaxMonthNumber )
VAR Result =
    IF (
        ShowData,
        CALCULATE (
            SUM ( 'fact'[FeeUSD] ),
            FILTER (
                ALLSELECTED ( 'fact' ),
                'fact'[MonthNumber] <= MAX ( 'dimMonthNames'[MonthNumber] )
            ),
            'fact'[Year] = Year
        )
    )
RETURN
    Result

How can I make it work and sliced by region. I have got slicers /Filters by Region, Year and Month. By default All Selected.

enter image description here

Solution

  • You need to make the region recognized in the calculation context.

    I added ALLSELECTED('dimRegion'[Region]) to ensure that the calculation respects any filters or slicers applied to the Region.

    YTD by Region =
VAR Year = MAX('Target'[Year])
VAR MaxMonthNumber = CALCULATE (
    MAX('fact'[MonthNumber]),
    'fact'[Year] = Year
)
VAR Result = CALCULATE (
    SUM('fact'[FeeUSD]),
    FILTER (
        ALLSELECTED('fact'),
        'fact'[MonthNumber] <= MaxMonthNumber
            && 'fact'[Year] = Year
    ),
    ALLSELECTED('dimRegion'[Region]) 
)
RETURN
    Result