Total not showing - SUMX ISSUE

[Anonymous]

Hi,

I'm a little rusty on DAX and I need your help.

The result of the measure is on this table:


The measure:

WAP_AWAP_BWAP 2 = 
//WAP calculation
var salesValue = CALCULATE(SUM(FACT_SALES_DATA[VALUE]))
var salesVolume = CALCULATE(SUM(FACT_SALES_DATA[DEMAND]))
var PricePer10Sticks = (DIVIDE(salesValue, salesVolume)*[Pack_Size])*10
var TotalsalesValue = CALCULATE(SUM(FACT_SALES_DATA[VALUE]), ALL(DASH_PRODUCT[FINAL_SKU_NAME]))
var TotalsalesVolume = CALCULATE(SUM(FACT_SALES_DATA[DEMAND]), ALL(DASH_PRODUCT[FINAL_SKU_NAME]))
var WAP = (DIVIDE(TotalsalesValue, TotalsalesVolume)*[Pack_Size])*10

//AWAP calculation
var salesValueAboveWAP = CALCULATE(SUM(FACT_SALES_DATA[VALUE]), FILTER(FACT_SALES_DATA, PricePer10Sticks > WAP))
var salesVolumeAboveWAP = CALCULATE(SUM(FACT_SALES_DATA[DEMAND]), FILTER(FACT_SALES_DATA, PricePer10Sticks > WAP))
var AWAP = (DIVIDE(salesValueAboveWAP, salesVolumeAboveWAP)*[Pack_Size])*10

//BWAP calculation
var salesValueBelowWAP = IF(ISBLANK((CALCULATE(SUM(FACT_SALES_DATA[VALUE]), FILTER(FACT_SALES_DATA, PricePer10Sticks < WAP)))),0,CALCULATE(SUM(FACT_SALES_DATA[VALUE]), FILTER(FACT_SALES_DATA, PricePer10Sticks < WAP)))
var salesVolumeBelowWAP = CALCULATE(SUM(FACT_SALES_DATA[DEMAND]), FILTER(FACT_SALES_DATA, PricePer10Sticks < WAP))
var BWAP =IF(ISBLANK((DIVIDE(salesValueBelowWAP, salesVolumeBelowWAP)*[Pack_Size])*10),0,(DIVIDE(salesValueBelowWAP, salesVolumeBelowWAP)*[Pack_Size])*10)

VAR AAA=SUMX(FILTER(FACT_SALES_DATA,PricePer10Sticks<WAP),FACT_SALES_DATA[VALUE])


RETURN
AAA

What changes do I need in the measure to have total as 39.771.436?

I tried SUMMARIZE and after SUMX but without any result.

Thank you!

[v-yiruan-msft · Accepted solution]

Hi  @Anonymous ,

You can create another new measure as below base on the measure [WAP_AWAP_BWAP 2] to get the correct total value and put this new measure to replace the original measure [WAP_AWAP_BWAP 2] onto the visual:

Measure = SUMX ( VALUES ( DASH_PRODUCT[FINAL_SKU_NAME] ), [WAP_AWAP_BWAP 2] )

In addition, you can refer the method in the following links to resolve it.

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

Best Regards

[lbendlin]

Your SUMX needs to be over the enumeration of FINAL_SKU_NAME.  Think of the column total as a separate table.