Visual Calculations/ DAX Measure - Total Incorrect on Table Visual

[Deelip]

Hi All,

I am reaching out for help on DAX. I know this a common issue in this forum. I tried different approaches but none of them gets me to the solution. I tried to use visual calculations and switched to DAX measure but neither of them is working.

My DAX measure is not removing the rowcontext filter for the totals, the result I am looking is my total should be the sum of values in the column. I have attached the sample of my data and dax 
My expected result for this selection should be (-115) which is sum of values in Availability, however the current result is based off the totals. 

DAX:

 SUMX(

    'product tbl',

    VAR _Key = 'product tbl'[Key]

    VAR _PO_QTY = CALCULATE(SUM(Availability[PO_Qty]))

    VAR _PREBOOK_QTY = CALCULATE(SUM(Availability[PreBookQty]))

    VAR _PLANTED_QTY =

                        SUMX(

                            FILTER('Dynamic', 'Dynamic'[plantState] = "Realized"),

                            'Dynamic'[quantity]

                        ) + CALCULATE(SUM(Overdue[quantity]))

    VAR _PLANNED_QTY =

                        SUMX(

                            FILTER('Dynamic', 'Dynamic'[planState] = "Budget"),

                            'Dynamic'[quantity]

                        ) + CALCULATE(SUM('External Bookings'[Booked Qty]))

    RETURN

        IF(

            NOT(ISBLANK(_PLANTED_QTY)),

            _PLANTED_QTY - _PO_QTY - _PREBOOK_QTY,

            _PLANNED_QTY - _PO_QTY - _PREBOOK_QTY

        )

)

Any help is much aprreciated!!

Thanks in advance. 

[v-venuppu]

Hi @Deelip ,

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.

[Anonymous]

Hi @Deelip ,

Did the reply offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.

Best regards,

Lucy Chen

[Greg_Deckler]

@Deelip First, please vote for this idea: https://community.fabric.microsoft.com/t5/Fabric-Ideas/Matrix-Table-grand-totals-with-Measures/idi-p...

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

[Deelip]

@Greg_Deckler Thank you! I did vote for this idea.

[rajendraongole1 · Accepted solution]

Hi @Deelip  - you should modify your measure using sumx on tht table.

Availability_Corrected =
SUMX(
'product tbl',
VAR _PO_QTY = CALCULATE(SUM('product tbl'[PO_Qty]))
VAR _PREBOOK_QTY = CALCULATE(SUM('product tbl'[PreBookQty]))
VAR _PLANTED_QTY =
SUMX(
FILTER('Dynamic', 'Dynamic'[plantState] = "Realized"),
'Dynamic'[quantity]
) + CALCULATE(SUM(Overdue[quantity]))

VAR _PLANNED_QTY =
SUMX(
FILTER('Dynamic', 'Dynamic'[planState] = "Budget"),
'Dynamic'[quantity]
) + CALCULATE(SUM('External Bookings'[Booked Qty]))

RETURN
IF(
NOT(ISBLANK(_PLANTED_QTY)),
_PLANTED_QTY - _PO_QTY - _PREBOOK_QTY,
_PLANNED_QTY - _PO_QTY - _PREBOOK_QTY
)
)

try this in report and hope it works.

[Deelip]

Hi @rajendraongole1 

Thank you for looking into this issue. I am still getting the incorrect result.

within this dax 
Availability_Corrected =
SUMX(
'product tbl',
VAR _PO_QTY = CALCULATE(SUM('product tbl'[PO_Qty]))
VAR _PREBOOK_QTY = CALCULATE(SUM('product tbl'[PreBookQty]))

My product tbl is dimension tbl it doesn't hold the columns PO and prebook quantities. These two columns are coming from a table called 'availability_tbl' . Product tbl is a dimension table which holds the product key and it is has a relaitonship to availability_tbl. 

FYI: Tables and their columns

Product_tbl = DIM product key (connected to all the below tables on key column)

Availability_tbl = POQTY and Prebook QTY(two different columns)

Dynamic_tbl = _Planted qty and Planned Qty ( these values are from same column but I am seprating)
Overedue_tbl = Overdue qty
External_bookings = Externalbookings_qty

Please let me know if you need more information.