Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
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
Your SUMX needs to be over the enumeration of FINAL_SKU_NAME. Think of the column total as a separate table.
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |