Compare sales to target net price

[mekaelj]

Hi,

I have a table in which all my sales lines end up. This data comes from an SQL server that updates with new lines from new sales daily. From this I have created another table where I've taken the average from each product group and put as a target price. I have several product group levels that I can drill down into and also sell my products in different countries.

What's annoying me is that I can't seem to create a measure that shows anything meaningful unless I slice down into one of the smaller product groups. The measure for the sales creates an average for all the sales, and since I don't sell the same amounts of each product the average sales price is vastly different from my target price, whereas if you drill down they're very similar.

How do I create some sort of measure that shows anything meaningful even when it's unfiltered? Attaching pictures to show what I mean. To the left is drilled down into a specific product whereas the right picture is the overall sales. 

My measures used are the following:

ASP Local Currency = DIVIDE([sales amount local currency];[Sales Q])

Target Net Price = AVERAGE(Price[Value])

Diff TNP ASP = IFERROR(([ASP Local Currency]-[Target Net Price])/[Target Net Price];BLANK())

[TomMartens]

Hey,

you have to go down to the detail row, by using AVERAGEX( 'table' , expression)

Averages Sales Price =
AVERAGEX('your sales table', [Sales Amount] / [Amount])

If you can't adapt this to you formual, it is helpful if you prepare some sample data and provide a link for downloading the file.

Hope this helps

[mekaelj]

Hi,

I've created a bit of sample data that roughly shows what I mean.

As you can see in the sample data with the example of Sweden, there's a 2.5% gap between the Average Sales Price and the Target Net Price. If you drill down into the product groups however, there's always a 0.00-0.01% gap. This kind of renders the unfiltered table a bit useless I feel as it's not showing any values of importance. 

Is there a way that I can make the unfiltered table show any kind of meaningful statistics?

Here are the files:

https://ufile.io/aggv4 (PBIX)

https://ufile.io/cpbbm (Data Excel)

Regards,

[TomMartens]

Hey,

I will have a closer look at the data in the evening, I have to earn my rent for now 😉

[TomMartens]

Sorry,

but the download is blocked by machine, can you share again on OneDrive or Dropbox?

Regards