Average Sales Price Matrix

[emarome94]

Hi guys, 

I have created a report in power bi where by calculating the difference between the ASP by customer/product combination and the previous year agreed price, if the first one is bigger than the second one, the result is moltiplied by the quantity sold. The result is what I want to see, the impact of bigger prices versus smaller ones that im going to call the formula.

The problem comes when I have to split this by month and retrieve the total: in fact, the total ytd is not showing the overall amount of the formula for every month but a "wrong" one. 

I think the wrong result is due to the fact that the formula, without any slicing, is taking the average sales price YTD instead aggregating by month (The month slicing is made it by a number value and not by date format)

This is the formula: 

Baseline SKU ASP Increase Effect (period) = CALCULATE(

    [SKU ASP Increase effect BL],

    FILTER( 'accouting', 'accounting'[Fiscal Period] >= RELATED( 'Price Lists'[Effective Period] )

)

)

The related function allow me to filter the calculation only on the period of the accounting data that is bigger or matching the effective period where the incremental price was agreed. 

This is the fiscal period table

2023001

2023002

...

2023012

Could someone help me on this? What would you do for correcting the formula? 

Thank you really in advance

E

[v-tangjie-msft · Accepted solution]

Hi @emarome94 ,

We can create  a measure. Place [Measure] on the [Values] field of the matrix.

Measure =

SUMX(

    SUMMARIZE(

        'Table',

        'Table'[RowName],

        'Table'[ColumnName],

        "total",[Baseline SKU ASP Increase Effect (period)]

    ),

    [total]

)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.