Price Development at Item Level with conditions

[sandeep_me]

Dear All, 

I have been sweating out for the last couple of days to calculate the price development % at item level on a certain condition. 

What I'm doing is price development on a monthly basis. From the below table, 'Customer' column is customer no (unique value) and 'item' is item no(unique value). 'Net value inc bonus' is sales , 'Sales qty' is sales quantity (all with Jan'22 as monthly filter). 'Avg Price CY' is current year average price (net value inc bonus/sales qty). Basic fundamentals of calculating pricing here is I look into sales of same items to same customer this year (selected month) vs whole of last year. So the 'keycust&item' is a unique key which will help us distinquish that. So I vlooked up a similar sheet with whole 2021 data for same item sold to same customer and arrived at the conclusion of the column 'Include both years'. If that's  'yes' I pick the sales for the current selected month sales in 'Sales CY for items both years', same logic for Avg price LY. 

Now the Price development % which I have a problem is the DAX in power BI. In the excel version at item level (lowest granularity) when I run the price development, for example the red highlighted, the logic here is (Avg Price CY/Avg Price LY-1). But when i got to summary level (or any upper heirarchy at BI report level), I need the logic to yellow highlighted which is ('Price development impact' total /Sales CY for items both years). ie in this case 471/11,300 =4.172%. 'Price development impact' is calculated by if(include both years="yes",(Avg Price CY/Avg Price LY-1)*'Sales Qty'). I have got this result in the DAX, but when there is only one item in the whole group I want to return logic for the item level at the summary level. 

Desired Result : 

In this BI report, you can see there is only one item (94000047) for entire group and the value 5.131% should be reading at the summary level of the heirarchies since it's only one item in the entire group. 

DAX

Below is the dax I came with which is not picking this particular condition. 

Price Change % Month-Last Year Customer _test1 =

VAR table1 =

CALCULATETABLE (

ADDCOLUMNS (

SUMMARIZE ( Attributes, Attributes[Customer], Attributes[Item],Attributes[UniqueKey]),

"SalesQty", [Sales Qty],

"SalesQtyLY", [Sales Qty LY],

"Sales_", [Sales],

"SalesLY", [Sales LY]

)

)

VAR table2 =

ADDCOLUMNS (

table1,

"Include",

IF (

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

|| ISBLANK ( [SalesLY] )

|| ISBLANK ( [Sales_] )

|| [SalesQty] <= 0

|| [SalesQtyLY] <= 0

|| [SalesLy] <= 0

|| [Sales_] <= 0,

FALSE,

TRUE

)

)

VAR table3 =

ADDCOLUMNS (

table2,

"AvgPrice", IF ( [Include] = TRUE, DIVIDE ( [Sales_], [SalesQty] ), 0 ),

"AvgPriceLY", IF ( [Include] = TRUE, DIVIDE ( [SalesLY], [SalesQtyLy] ), 0 ),

"Result",

IF ( [Include] = TRUE, [Sales_],0)

)

VAR AveragePriceCurrentYear = SUMX ( FILTER ( table3, [Include] = TRUE ), [AvgPrice] )

VAR AveragePriceLastYear = SUMX ( FILTER ( table3, [Include] = TRUE ), [AvgPriceLY] )

VAR SalesForItemCustomerBothPeriods= SUMX ( FILTER ( table3, [Include] = TRUE ), [Result] )

VAR Conditions=COUNTAX(FILTER(table3,[Include]=TRUE),[Include])=1

VAR PriceDev = IF(ISBLANK(DIVIDE(AveragePriceCurrentYear,AveragePriceLastYear)),BLANK(),DIVIDE(AveragePriceCurrentYear,AveragePriceLastYear)-1)

RETURN

IF (

ISINSCOPE(Attributes[Item]) || Conditions,IF(ISBLANK(DIVIDE(AveragePriceCurrentYear,AveragePriceLastYear)),BLANK(),DIVIDE(AveragePriceCurrentYear, AveragePriceLastYear)-1),

[Price Change Impact Month-Last Year Customer_test]/ SalesForItemCustomerBothPeriods

)

Any help would be very helpful. 

Thanks in advance 

Sandeep

[v-yiruan-msft · Accepted solution]

Hi @sandeep_me ,

Please create another new measure as below and put it onto the matrix visual to replace the measure [Price Change % Month-Last Year Customer _test1] , later check whether the matrix can dispaly the correct values:

New Measure =
SUMX (
    GROUPBY (
        'Table',
        'Table'[Product Category],
        'Table'[Product Type],
        'Table'[Product Family],
        'Table'[Product Group],
        'Table'[Item],
        'Table'[Item Desc]
    ),
    [Price Change % Month-Last Year Customer _test1]
)

Best Regards