Forum Discussion

Deelip's avatar
Deelip
Icon for Resolver I rankResolver 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 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. 

 

 

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

6 Replies

  • 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
      Icon for Resolver I rankResolver I

      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. 

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable

    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

  • v-venuppu's avatar
    v-venuppu
    Icon for Community Support rankCommunity Support

    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.