Forum Discussion

leandroxps's avatar
leandroxps
Frequent Visitor
2 years ago
Solved

Matrix Visual: Row subtotal blank/0 using DAX for custom periods

Hi all!   I'm developing some matrix visuals to analyze machine costs per month, but over the year. But I'm trying to show row total per year and my formula is not showing. I'm assuming the probl...
Need Help
  • leandroxps's avatar
    leandroxps
    2 years ago

    If somebody is getting the same problem, I found a solution to make it works correctly.

    I considered that has no value of my CALENDAR TABLE in Subtotal Column, then I got the right result for my subtotal using a new measure.

     

    For example:

    TotalCost12Month =
    VAR MaxDate = MAX(TBL_CALENDAR_1[DATA])
    VAR MaxDate12Month = EOMONTH(MaxDate, -13)
    VAR Result =
    IF (
    HASONEVALUE(TBL_CALENDAR_12[DATA]) &&
    MAX(TBL_CALENDAR_12[DATA]) <= MaxDate &&
    MIN(TBL_CALENDAR_12[DATA]) > MaxDate12Month,
    CALCULATE (
    SUMX('_MEASURES_MCHN',[TotalCost]*1),
    FILTER (
    ALL (
    TBL_CALENDAR_1[DATA]
    ),TBL_CALENDAR_1[DATA] = VALUES (TBL_CALENDAR_12[DATA])
    )
    ),
    IF(NOT(HASONEVALUE(TBL_CALENDAR_12[DATA])),[TotalMonthsBefore],BLANK())
    )
    RETURN Result

     
    In this scenario I have 2 totals:
    1) TotalCost: Considering cost per month
    2) TotalMonthsBefore: Considering the total for your custom period.
     
    TotalMonthsBefore =
    CALCULATE (
        SUM (FACT_MACHINES[COST]),
        FILTER (
            ALL (TBL_CALENDAR_1[DATE]),
            TBL_CALENDAR_1[DATE] <= MAX(TBL_CALENDAR_1[DATE]) &&
            TBL_CALENDAR_1[DATE] >= EOMONTH (MAX(TBL_CALENDAR_1[DATE]),-12 <here you can use how many months you want to consider for this measure>)
        )
    )
leandroxps's avatar
leandroxps
Frequent Visitor
2 years ago

If somebody is getting the same problem, I found a solution to make it works correctly.

I considered that has no value of my CALENDAR TABLE in Subtotal Column, then I got the right result for my subtotal using a new measure.

 

For example:

TotalCost12Month =
VAR MaxDate = MAX(TBL_CALENDAR_1[DATA])
VAR MaxDate12Month = EOMONTH(MaxDate, -13)
VAR Result =
IF (
HASONEVALUE(TBL_CALENDAR_12[DATA]) &&
MAX(TBL_CALENDAR_12[DATA]) <= MaxDate &&
MIN(TBL_CALENDAR_12[DATA]) > MaxDate12Month,
CALCULATE (
SUMX('_MEASURES_MCHN',[TotalCost]*1),
FILTER (
ALL (
TBL_CALENDAR_1[DATA]
),TBL_CALENDAR_1[DATA] = VALUES (TBL_CALENDAR_12[DATA])
)
),
IF(NOT(HASONEVALUE(TBL_CALENDAR_12[DATA])),[TotalMonthsBefore],BLANK())
)
RETURN Result

 
In this scenario I have 2 totals:
1) TotalCost: Considering cost per month
2) TotalMonthsBefore: Considering the total for your custom period.
 
TotalMonthsBefore =
CALCULATE (
    SUM (FACT_MACHINES[COST]),
    FILTER (
        ALL (TBL_CALENDAR_1[DATE]),
        TBL_CALENDAR_1[DATE] <= MAX(TBL_CALENDAR_1[DATE]) &&
        TBL_CALENDAR_1[DATE] >= EOMONTH (MAX(TBL_CALENDAR_1[DATE]),-12 <here you can use how many months you want to consider for this measure>)
    )
)