Forum Discussion

Vayne-Daryl's avatar
Vayne-Daryl
New Member
2 years ago
Solved

Matrix Row and Columns Subtotal are not calculating correctly

Hello Good Day Everyone 🙂 I am struggling to find a solution for the incorrect matrix sub total. Hope someone could help me out or advice a workaround somehow. Your help is very much appreciated....
Need Help
  • anmolmalviya05's avatar
    anmolmalviya05
    2 years ago

    Hi Vayne-Daryl, Hope you are doing good!

    Please try the below measures

     

    Test Measure =
    VAR CurrentFiscalYear = SELECTEDVALUE('Calendar'[Fiscal Year])
    VAR StartFiscalMonth =
    CALCULATE(
    MIN('ApplicationCost'[StartDateFiscalMonth]),
    FILTER(
    'ApplicationCost',
    'ApplicationCost'[ApplicationName] = SELECTEDVALUE('ApplicationCost'[ApplicationName]) &&
    'ApplicationCost'[Control Number] = SELECTEDVALUE('ApplicationCost'[Control Number]) &&
    'ApplicationCost'[FiscalYear] = CurrentFiscalYear
    )
    )
    VAR Result =
    SUMX(
    'ApplicationCost',
    IF(
    'ApplicationCost'[FiscalYear] = CurrentFiscalYear &&
    'ApplicationCost'[StartDateFiscalMonth] <= MAX('Calendar'[Fiscal Month]),
    'ApplicationCost'[Value],
    0
    )
    )
    RETURN
    Result

     

     

     

     

    Final Measure =
    SUMX(
    SUMMARIZE(
    'ApplicationCost',
    'ApplicationCost'[ApplicationName],
    'ApplicationCost'[Control Number],
    "MonthValue", [Test Measure]
    ),
    [MonthValue]
    )

     

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

     

anmolmalviya05's avatar
anmolmalviya05
Super User
2 years ago

Hi Vayne-Daryl, Hope you are doing good!

Please try the below measures

 

Test Measure =
VAR CurrentFiscalYear = SELECTEDVALUE('Calendar'[Fiscal Year])
VAR StartFiscalMonth =
CALCULATE(
MIN('ApplicationCost'[StartDateFiscalMonth]),
FILTER(
'ApplicationCost',
'ApplicationCost'[ApplicationName] = SELECTEDVALUE('ApplicationCost'[ApplicationName]) &&
'ApplicationCost'[Control Number] = SELECTEDVALUE('ApplicationCost'[Control Number]) &&
'ApplicationCost'[FiscalYear] = CurrentFiscalYear
)
)
VAR Result =
SUMX(
'ApplicationCost',
IF(
'ApplicationCost'[FiscalYear] = CurrentFiscalYear &&
'ApplicationCost'[StartDateFiscalMonth] <= MAX('Calendar'[Fiscal Month]),
'ApplicationCost'[Value],
0
)
)
RETURN
Result

 

 

 

 

Final Measure =
SUMX(
SUMMARIZE(
'ApplicationCost',
'ApplicationCost'[ApplicationName],
'ApplicationCost'[Control Number],
"MonthValue", [Test Measure]
),
[MonthValue]
)

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

 

  • VayneDaryl's avatar
    VayneDaryl
    Frequent Visitor
    2 years ago

    Hello anmolmalviya05,
    Thank you so much for the help I really appreciate it and your solution works like a charm 🙂
    I have notice that you have created another measure which solved the issue.
    Thanks again for the support and sharing your ideas 🙂