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 Delta LV column, however the row subtotal is showing correctly whereby it is actually factory ATKL platform J750 +T33XX + UP1600B= 335,058 and the same for factory KTM, however the grand total supposed to be 333,058 + 291,929=624,987 but instead it is showing 3,453,073, could anyone please help me with this issue, thank you very much.

 

  • 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!





     

8 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    I am not sure how your semantic model looks like, but please try something like below whether it works.

     

    new measure: =
SUMX (
    SUMMARIZE (
        your_fact_table,
        factory_dim[Factory column],
        platform_dim[Platform column],
        calendar_dim[WW column]
    ),
    [Delta LV 1 measure]
)

     

  • 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 

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.

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

    Hi sandy_liew 

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

    Thank you.

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

    Hi sandy_liew 

    I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

    Thank you.