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.

rajendraongole1's avatar
rajendraongole1
Super User
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.

Deelip's avatar
Deelip
Resolver I
1 year ago

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.