Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago
Solved

Average price increase

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:

flitvak_0-1630613618149.png

Table of facts:

flitvak_1-1630613665413.png

Output shown by Power BI:

flitvak_3-1630613737077.png

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

  • Anonymous's avatar
    Anonymous
    4 years ago

    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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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)

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    5 years ago

    @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
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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