Average price increase

[Syndicate_Admin]

Good afternoon, I was asked to analyze the price variations of different items individually and grouped by their category (Furniture, shelves, armchairs). I have the problem to calculate the average increase per category given that there are items that were stopped selling this month and vice versa.

Here is an example of tables with dummy data:

Table of dimensions of articles:

Table of facts:

Output shown by Power BI:

The problem is for example in the category "armchairs" article 7 stopped selling but wanting to get the average increase of that category Power Bi does (16 + 10 + 20) / (6 + 10 + 8 + 16) when in reality it should do (16 + 10 + 20) / (10 + 8 + 16).

Power Bi's measure for getting the current price and the previous price are as follows:

Current price = IF(CALCULATE(sum(FAC_QueryListaCostos[Reference cost source currency]);

FILTER('Calendar Table';' Calendar table '[Offset of the current month]=1);

FILTER(FAC_QueryListaCostos; FAC_QueryListaCostos[Articulo]="DIS4BL"))<>BLANK();

CALCULATE(sum(FAC_QueryListaCostos[Source currency reference cost]);

FILTER('Calendar Table';' Calendar table '[Offset of the current month]=1));

CALCULATE(sum(FAC_QueryListaCostos[Source currency reference cost]);

FILTER('Calendar Table';' Calendar table'[Offset of the current month]=0)))

Previous price = IF(CALCULATE(sum(FAC_QueryListaCostos[Reference cost source currency]);

FILTER('Calendar Table';' Calendar table '[Offset of the current month]=1);

FILTER(FAC_QueryListaCostos; FAC_QueryListaCostos[Articulo]="DIS4BL"))<>BLANK();

CALCULATE(sum(FAC_QueryListaCostos[Source currency reference cost]);

FILTER('Calendar Table';' Calendar table '[Offset of the current month]=0));

CALCULATE(sum(FAC_QueryListaCostos[Source currency reference cost]);

FILTER('Calendar Table';' Calendar table'[Offset of the current month]=-1)))

Thank you very much for the help !!!!!

Fred

[v-jayw-msft · Accepted solution]

Hi @Syndicate_Admin ,

Check the formula.

Measure 3 = 
var act = CALCULATE(SUM('fact'[precio actual]),FILTER(ALL('fact'),'fact'[articulo] in VALUES('dimensions'[articulo])))
var ant = CALCULATE(SUM('fact'[precio anterior]),FILTER(ALL('fact'),'fact'[articulo] in VALUES('dimensions'[articulo]) && 'fact'[precio actual] <> BLANK()))
return
(act-ant)/ant

Best Regards,

Jay

[Syndicate_Admin]

@Syndicate_Admin thank you very much for the help!!! Trying to replicate the suggested measurement the result showing the Power Bi is not correct. I don't know exactly what he is doing but as an example, a category that should give an increase of 7.32% shows me 12.88%. The measure was like this:

Measure 1 =

var priceCurrent = [Current price]

var pricePrevious = [Previous price]

return

DIVIDE(sumx(FILTER(values(DIM_Articulos); NOT(ISBLANK(precioAnterior)));precioActual);precioAnterior)

Modifying some things to the extent I came to the same result by which the original query was generated. That is to say, it continues to consider for the calculation of the average by category items whose sale was discontinued.

The adapatada measure with which I arrived at the same erroneous "original" resutado is:

Measure 2 =

var current price = [current price]

var pricePrevious = [Previous price]

return

DIVIDE(sumx(FILTER(values(DIM_Articulos[Clasificador 7 articulos]); NOT(ISBLANK(precioAnterior)));precioActual);precioAnterior)-1

Thank you very much for the help!!!!

Best regards

[amitchandak]

@Syndicate_Admin , Try like, assuming your [Current price] and [Previous price] are working fine

Meausre =
Var _1 = [Current price]
var _2 = [Previous price]
return
divide(sumx(filter(values(Table[Category]), not(isblank(_2))),_1),_2)