DAX in a new column

[sportive92003]

Hello,

I have added a new column to categorize my products. You can see my catecories (description_categorie). I have a categorie named "Total" (N=27 476) and "Total (frais)" N=11 425). My category "Total" should be 16 051 (27 425 - 11 425). How to get this result without changing the name of my column (Descrition_categorie) as I ha ve in many graphs I don't want to change them.

[v-achippa · Accepted solution]

Hi @sportive92003,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @123abc and @MasonMA for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[MasonMA]

Hi,

Can i ask how you added this 'new column to categorize products'? If this was created by DAX or Power Query, i'd suggest maybe fixing the logic in that column so “Total” no longer includes “Total (frais)” items? This avoids needing an adjustment measure altogether.

[sportive92003]

Yes, I click on transform Data and ADD A COLUMN

Show in another way (I cut a long part as it's all the same programmation): 

= Table.AddColumn(#"Valeur remplacée", "Personnalisé", each if Text.Contains([description], "BLC-1500 LAVE-GLACE BIO") then "Antigel - produits chimiques" else if [description] = "URÉE" then "Urée" else if [description] = "UREE" then "Urée" else if Text.StartsWith([description], "TOTAL") then "Total" else if Text.StartsWith([description], "ATS") then "ATS" else if [description] = "HUILE A FORME ISO 55" then "Envirolub" else if Text.StartsWith([description], "ENVIROLIN") then "Envirolin" else if [description] = "FRAIS DE TRANSPORT LUBRIFIANTS" then "Total (frais)" else "À VOIR")

The results concern amount, Profit and Volume

[MasonMA]

deleted.. 

[sportive92003]

No, I need to calculate "Total" minus "Total (frais)" to get the real amount/profit/volume of "Total".  

[MasonMA]

Hi, i thought you were looking to adjust your logic for the column. If only to calculate the Total minus Total (frais). You will need to use Measures as @123abc suggesed. 

Create measures like 

Amount := SUM('table'[Amount])

Total =
CALCULATE([Amount], 'table'[Description_categorie] = "Total")

TotalFrais =
CALCULATE([Amount], 'table'[Description_categorie] = "Total (frais)")

Measure= 

SWITCH(
SELECTEDVALUE('table'[Description_categorie]),
"Total", Total
[Amount]
)

[sportive92003]

Hello,

I did this DAX programmation and it works! See below. The problem are:

1- I have to change aaaaallllll my graphics to use this new variable (QTÉ (switch)). How can I keep my usual variable included in my database (Qtée stock)? I have used this variable in multiple dax programmation and graphics as well, so it's complicated to check all of them without errors...

2- I will have to do the same for variable Profit and Amount. 

So, how can I program : if "Qté Stock" is used, use "Qté (switch)" variable instead. Or rename Qté (switch) to Qté Stock would be easier, than I change nothing in my graphics and programmatiosn. Same for profit and Amount as well...

QTÉ (switch) =

SWITCH(

SELECTEDVALUE('SG LUBRIFIANTS - 2019-2023 data corrigées'[Description_catégorie]),

"Total",[Total - Total (frais) QTÉ], [Qtée stock Measure])

[MasonMA]

Hi,

I would rename the new measure to the old measure name and also test it first in a duplicated file. 

By that you may replace the logic of Qtée stock Measure with the SWITCH logic, delete the temporary QTÉ (switch) measure and rename your updated Qtée stock Measure back to Qté Stock.

[123abc]

You don’t need to rename your existing column; you can handle this with a DAX measure or a calculated column that adjusts the value for “Total” without changing the original category names.

[sportive92003]

Could you help me to reach it ?

[123abc]

sure