Forum Discussion
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
- rajendraongole1
Super User
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
Resolver I
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 columnsProduct_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_qtyPlease let me know if you need more information.
- Greg_Deckler
Community Champion
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
Resolver I
Greg_Deckler Thank you! I did vote for this idea.
- AnonymousNot 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