price elasticity compared to sales variation

Airwen · ‎08-21-2024

Hi,
I want to create a dynamic measure of elasticity. It's based on the weekly variation of the unit price of an article compared to the sales of the same article. The unit price is in the "achats" table [Prix Unitaire HT]. The sale is in the "ventes" table [CA HT Net].
The results don't seem plausible.
There is a calculated column in the achats table to calculate the variation on each line. This may not be the right way to go.
Please find the pbix file :
https://drive.google.com/file/d/1BNZ1Jgym1Rj4C9aEUi-22JkxcKMxiiHi/view?usp=sharing
Bellow the relations :

I would like to be able to create a dynamic visual where the elasticity is displayed by week and can be modified with a year segment.
On the same page, there is a [code_famille] from the liste_produits table visual and [type de commerce] from "clients". It would be nice to filter the result on these two variables as well.
I've search and tried several measures so far :

ELA 009 moyenne V° PU achat = 
IF(
    ISINSCOPE('clients'[Code Client]),
    AVERAGEX(
        SUMMARIZE(
            'ventes',
            'ventes'[Code Client],
            'clients'[Type commerce],
            'calendrier'[semaine],
            'calendrier'[Année],
            "VariationPU", 
            CALCULATE(
                AVERAGEX(
                    RELATEDTABLE('achats'),
                    'achats'[VariationPU]
                )
            )
        ),
        ROUND([VariationPU],2)
    ),
    IF(
        ISINSCOPE('liste_produits'[Référence Article]),
        AVERAGEX(
            SUMMARIZE(
                'ventes',
                'ventes'[Référence Article],
                'calendrier'[semaine],
                'calendrier'[Année],
                "VariationPU", 
                CALCULATE(
                    AVERAGEX(
                        RELATEDTABLE('achats'),
                        'achats'[VariationPU]
                    )
                )
            ),
            ROUND([VariationPU],2)
        ),
        IF(
        ISINSCOPE('liste_produits'[code_famille]),
        AVERAGEX(
                SUMMARIZE(
                'ventes',
                'ventes'[Référence Article],
                'liste_produits'[code_famille],
                'calendrier'[semaine],
                'calendrier'[Année],
                "VariationPU", 
                CALCULATE(
                    AVERAGEX(
                        RELATEDTABLE('achats'),
                        'achats'[VariationPU]
                    )
                )
            ),
            ROUND([VariationPU],2)
        ),
        IF(
        ISINSCOPE('clients'[Type commerce]),
        AVERAGEX(
            SUMMARIZE(
                'ventes',
                'ventes'[Référence Article],
                'clients'[Type commerce],
                'calendrier'[semaine],
                'calendrier'[Année],
                "VariationPU", 
                CALCULATE(
                    AVERAGEX(
                        RELATEDTABLE('achats'),
                        'achats'[VariationPU]
                    )
                )
            ),
            ROUND([VariationPU],2)
        ),
        BLANK()  -- Retourne BLANK si ni code client ni référence article n'est dans le contexte
    )
)
)
)

ELA 010 V% ventes S-1 = 
VAR CurrentWeek = 
    IF(
        ISBLANK(SELECTEDVALUE('calendrier'[semaine])),
        MAX(calendrier[Semaine]),
    SELECTEDVALUE('calendrier'[semaine])
    )
VAR CurrentYear = 
     IF(
        ISBLANK(SELECTEDVALUE('calendrier'[Année])),
        MAX(calendrier[Année]),
    SELECTEDVALUE('calendrier'[Année])
    )
VAR LastYear = CurrentYear - 1
VAR PreviousWeekSales =
    IF(
        CurrentWeek = 1,
        CALCULATE(
            [CA 001 total ventes],
            FILTER(
                ALL('calendrier'),
                calendrier[Année] = LastYear &&
                calendrier[Semaine] = 
                    MAXX(
                        FILTER(
                            ALL('calendrier'),
                            calendrier[Année] = LastYear
                        ),
                        calendrier[Semaine]
                    )
            )
        ),
        
        CALCULATE(
            [CA 001 total ventes],
            FILTER(
                ALL('calendrier'),
                calendrier[Année] = CurrentYear &&
                calendrier[Semaine] = CurrentWeek - 1
            )
        
        )
)
VAR CurrentWeekSales = [CA 001 total ventes]

RETURN
    IF(
        NOT ISBLANK(PreviousWeekSales),
        ROUND(DIVIDE(CurrentWeekSales - PreviousWeekSales, PreviousWeekSales, 0),2),
        BLANK()
    )

ELA 012 moy V° PU semaine = 
    AVERAGEX(
        SUMMARIZE(
            achats,
            calendrier[Année],
            calendrier[Semaine],
            liste_produits[Référence Article],
            "VariationPU",
            AVERAGEX(achats,achats[VariationPU])
        ),
    ROUND([VariationPU],2)
    )

ELA 001 elasticité semaine = 
DIVIDE(
    [ELA 010 V% ventes S-1],
   [ELA 012 moy V° PU semaine],0
    )

ELA 001 elasticité client&article = 
DIVIDE(
    [ELA 010 V% ventes S-1],
   [ELA 009 moyenne V° PU achat],0
    )

The results don't seem plausible.
Please find bellow the pbix file :
https://drive.google.com/file/d/1BNZ1Jgym1Rj4C9aEUi-22JkxcKMxiiHi/view?usp=sharing

Thanks

Anonymous · ‎08-21-2024

Hi @Airwen

I can't get a good understanding of your needs with the information you've provided. First of all, in the pbix you provided, I can't find the Code Client column in the clients table, but in your DAX you wrote this column:

Please confirm the integrity of your data or did you write the wrong table in DAX?

Also I see that you are using virtual tables in your DAX, but your table clients doesn't have any relationship with other tables, so DAX can't form virtual tables with columns from that table with columns from other tables.

What exactly is the purpose of the clients table, please? Perhaps you can go ahead and merge the tables that need to be merged into one entity table first in Power Query and then do the calculations in that entity table.

Could you please show your expected results in Excel or screenshots? That would be very helpful.

Best Regards,
Yulia Xu

Airwen · ‎08-22-2024

Hi @Anonymous
If I'm not mistaken, [code client] is the first column in the clients table.

The clients table is connected to Ventes tables. As liste_produit is also connected to the vente tables, i assumed it would be possible to select those variables.

The achats tables don't contain a column code client as they only contain the purchase operations. However, I would like to be able to show the price elasticity for both "reference item" and "code client". The unit price of purchases has a direct impact on the unit price of sales. While we are talking about it, I just realised that it might be easier to get the unit price only from the "ventes" table.

The customers table contains the data about the location, the commercial, the type of shop, etc. These variables are useful to get an overall elasticity between different types of store or commercial's wallet.

Here is the expected results :

date	week	code client	référence article	elasticity
25/02/2024	9	G107	1002	1
31/03/2024	14	G107	952	0,7
05/05/2024	19	T685	663	-0,5
09/06/2024	24	T685	523	-2
14/07/2024	29	G203	1020	3

It would then be possible to present the elasticity variable in a histogram by week.

There are two tables on the same page:

1/ reference artciles;elasticity
2/ code client; elasticity
The result should be dynamic by filtering the date (year, week, month) or by choosing a type of shop.

Kind regards