Forum Discussion

Deelip's avatar
Deelip
Resolver I
1 year ago
Solved

Visual Calculations/ DAX Measure - Total Incorrect on Table Visual

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 swi...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
1 year ago

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

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's avatar
    Deelip
    Resolver I
    1 year ago

    Greg_Deckler Thank you! I did vote for this idea.