Grand Total for Matrix Visual not displaying correctly

[sandy_liew]

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]

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.

[v-karpurapud]

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]

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]

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]

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]

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 · Accepted solution]

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!

[Jihwan_Kim]

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]
)