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>)
        )
    )
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

leandroxps First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

leandroxps's avatar
leandroxps
Frequent Visitor
2 years ago

Hi Greg!

Thanks for the fast reply.
I was considering this solution, but my main problem is that I can't summarize my dates (or at least I dont know how to do it correctly), because my tables are not in relationship directly.

If I apply my simple DAX directly in a Matrix (TotalCost = SUM(FACT_MACHINES[COST])), or apply this logic you sent, it works fine for the actual period of my slicer.

But in this case where I have to filter a single date and bring months before, it's giving me some headache.

Here's a example:


My first matrix is with this solution you sent. It shows me all totals (line and column), but only for the actual month.

If I apply the formula for 12 months (my dates are dd/MM/yyyy pattern), my row totals disappear.
The second is using my DAX formula where I get months from an inactive relationship from my main date table.

Am I doing something wrong in my DAX formula?