Forum Discussion

sandy_liew's avatar
sandy_liew
Frequent Visitor
1 year ago
Solved

Grand Total for Matrix Visual not displaying correctly

Hi, I have the DAX measure shown below for Delta LV column where it is actually obtained from the values of "Outs' -"LV", I would like to display the row subtotals as well as row grand total for the ...
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    Hi  sandy_liew 

    Try considering the below steps:

    Create the Numeric Measure

    Delta LV Numeric = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE('Grand_Total', 'Grand_Total'[Factory], 'Grand_Total'[Platform]),
        "Delta", [Actual Outs] - CALCULATE(SUM('Grand_Total'[LV]))
    ),
    [Delta]
)



    Create the Formatted Display Measure

    Delta LV Formatted = 
VAR DeltaLV = [Delta LV Numeric]
RETURN
    IF(
        ISBLANK(DeltaLV),
        BLANK(),
        IF(
            DeltaLV < 0,
            "(" & FORMAT(ABS(DeltaLV), "#,0") & ")",
            FORMAT(DeltaLV, "#,0")
        )
    )

     

     


     

    If this response is helpful, please mark it as an Accepted Solution to benefit other community members.
     

    Thank You!





     

v-karpurapud's avatar
v-karpurapud
Icon for Community Support rankCommunity Support
1 year ago

Hi sandy_liew 

Thank you for reaching out to the Microsoft Fabric Community Forum.

Regarding the grand Total for Matrix Visual I’ve created a sample .pbix file to demonstrate one possible solution.
Please consider the below DAX:

Delta LV 1 =

SUMX (

    VALUES('Grand_Total'[Platform]),

    CALCULATE(SUM('Grand_Total'[Outs]) - SUM('Grand_Total'[LV]))

)

 

If this response resolves your query, kindly mark it as an Accepted Solution to assist other community members. A Kudos is also appreciated if you found the response helpful.

Additionally, thank you Jihwan_Kim for responding this topic.

 

Thank You!



 

 



 

sandy_liew's avatar
sandy_liew
Frequent Visitor
1 year ago
Delta LV =
VAR DeltaLV = [Actual Outs] - SELECTEDVALUE(T2_DRR_Calendar[DRR (Week)])
RETURN
    IF(DeltaLV < 0, "(" & FORMAT(ABS(DeltaLV), "#,0") & ")", FORMAT(DeltaLV, "#,0"))

The above is actually the DAX formula in order to get my [Delta LV] measure, the [DRR (Week)] is similar to [LV] whereby it is originally obtained from csv file while [Actual Outs] is a measure that I created and obtained the data from Oracle database
  • sandy_liew's avatar
    sandy_liew
    Frequent Visitor
    1 year ago

    Hence, I don't think I'm able to use the method that you tried in the grand total pbix file, could u pls help me to look further into this question? Thanks

     

    • v-karpurapud's avatar
      v-karpurapud
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi  sandy_liew 

      Try considering the below steps:

      Create the Numeric Measure

      Delta LV Numeric = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE('Grand_Total', 'Grand_Total'[Factory], 'Grand_Total'[Platform]),
        "Delta", [Actual Outs] - CALCULATE(SUM('Grand_Total'[LV]))
    ),
    [Delta]
)



      Create the Formatted Display Measure

      Delta LV Formatted = 
VAR DeltaLV = [Delta LV Numeric]
RETURN
    IF(
        ISBLANK(DeltaLV),
        BLANK(),
        IF(
            DeltaLV < 0,
            "(" & FORMAT(ABS(DeltaLV), "#,0") & ")",
            FORMAT(DeltaLV, "#,0")
        )
    )

       

       


       

      If this response is helpful, please mark it as an Accepted Solution to benefit other community members.
       

      Thank You!