Average Price reading differently on YTD and LYTD differently

[sandeep_me]

Hello Colleagues, 

I have a very weird problem. I have an Avg Price YTD and Avg Price YTD with some conditions and same DAX. The Avg Price YTD is giving me accurate answer and LYTD is giving me inconsistent answer. Below is the screenshot. 

Here the "220152" is item no and below is the customer. As you can see  'cross' marked are wrong values and at higher item level hierarchy the value is always wrong, but when we drill down we have right and wrong answers. Below are the DAX for YTD and LYTD

Average Price_YTD_Cust/Item =

VAR table1 =

CALCULATETABLE (

ADDCOLUMNS (

SUMMARIZE ( fact_CustInvoice,fact_CustInvoice[id_dim_Customer],fact_CustInvoice[id_dim_Item]),

"SalesQty", [Sales Qty YTD],

"SalesQtyLY", [Sales Qty LYTD],

"Sales_", [Sales YTD],

"SalesLY", [Sales LYTD]

),

ALL('pbi vdim_Date'),DATESYTD('pbi vdim_Date'[Date_bk_Id])

)

VAR table2 =

ADDCOLUMNS (

table1,

"Include",

IF (

ISBLANK ( [SalesQty] ) || ISBLANK ( [SalesQtyLY] )

|| ISBLANK ( [SalesLY] )

|| ISBLANK ( [Sales_] )

|| [SalesQty] <= 0

|| [SalesQtyLY] <= 0

|| [SalesLy] <= 0

|| [Sales_] <= 0,

FALSE,

TRUE

)

)

RETURN

CALCULATE(DIVIDE([Sales YTD],[Sales Qty YTD]),FILTER(table2,[Include]=TRUE()))

Average Price_LYTD_Cust/Item =

VAR table1 =

CALCULATETABLE (

ADDCOLUMNS (

SUMMARIZE ( fact_CustInvoice,fact_CustInvoice[id_dim_Customer],fact_CustInvoice[id_dim_Item]),

"SalesQty", [Sales Qty YTD],

"SalesQtyLY", [Sales Qty LYTD],

"Sales_", [Sales YTD],

"SalesLY", [Sales LYTD]

),

ALL('pbi vdim_Date'),DATESYTD('pbi vdim_Date'[Date_bk_Id])

)

VAR table2 =

ADDCOLUMNS (

table1,

"Include",

IF (

ISBLANK ( [SalesQty] ) || ISBLANK ( [SalesQtyLY] )

|| ISBLANK ( [SalesLY] )

|| ISBLANK ( [Sales_] )

|| [SalesQty] <= 0

|| [SalesQtyLY] <= 0

|| [SalesLy] <= 0

|| [Sales_] <= 0,

FALSE,

TRUE

)

)

RETURN

CALCULATE(DIVIDE([Sales LYTD],[Sales Qty LYTD]),FILTER(table2,[Include]=TRUE()))

Highlighed is where the value is calculated. I have also tried in written SUMX but at that point the summary value against the item "220152" adds up, but below values are accurate. 

Any advice would be super helpful. 

Thanks in advance

KR,

Sandeep

[v-yiruan-msft]

Hi @sandeep_me,

According to your description, it seems that the measure [Average Price_LYTD_Cust/Item] does not get the correct value, either in the total or in each sub-level section. Are the values of [Sales YTD], [Sales LYTD], [Sales QTY YTD] [Sales QTY LYTD] and [Average Price_YTD_Cust/Item] referenced by this measure all correct? Please create a new measure based on [Average Price_LYTD_Cust/Item] as follows:

Measure =
SUMX (
    GROUPBY ( 'fact_CustInvoice', [Product Category], [Product], [Item] ), //The fields which be put on Rows options of matrix visual
    [Average Price_LYTD_Cust/Item]
)

You can also refer the following threads 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

If the above ones can't help you get the correct results, please share a simplified pbix file(exclude sensitive data) with us in order to provide you a suitable solution. Thank you.

Best Regards

[sandeep_me]

@amitchandak : Avg Price is with a condition as you can see. [Sales YTD] and [Sales LYTD] is already a calculated measure. Below is the DAX 

Sales YTD = CALCULATE ( [Sales] , ALL ( 'pbi vdim_Date' ), DATESYTD( ( 'pbi vdim_Date'[Date_bk_Id] ) ))

Sales Qty LYTD = CALCULATE ( [Sales Qty], DATEADD ( 'pbi vdim_Date'[Date_bk_Id], -1, YEAR ), ALL ( 'pbi vdim_Date' ) )

I tried as you suggested but it's not working. 

[amitchandak]

@sandeep_me , Not very clear.

Is avg price like

Avg Price = Divide( Sum(sales[sales]) , Sum(sales[Qty]) )

then try

YTD Sales = CALCULATE([Avg Price],DATESYTD('Date'[Date],"12/31"))

Last YTD = CALCULATE([Avg Price],DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA