topic Re: Switch category in DAX Commands and Tips

Switch category

sportive92003 — Tue, 23 Jul 2024 18:23:15 GMT

Hello,

I have one client X who is in the sector "20%...". I have to eliminate this category and put 33% of the amount in sector "01-..."; 33% in sector "02-..." and 33% in sector "07-...". On the graphic, if I select client X, I want to see the amount divided in these 3 sectors. If I select sector "01-Machinerie", or 02 or 07 the 33% of this client mus be included in each. I have 7 sectors in total, must have 6. Any idea?

Thanks!

Re: Switch category

BeaBF — Wed, 24 Jul 2024 10:39:36 GMT

sportive92003 you need to create a column to switch the category, can you share the file pbix o paste sample data?

BBF

Re: Switch category

sportive92003 — Wed, 24 Jul 2024 11:48:22 GMT

Hello BeaBF,

yes, here are the datas. I don't see join a piece, sorry. I don't have sector 7 in this example so just use sector 4 instead (so 33% in sector 1, 33% sector 2 and 33% sector 4)

Secteur	Somme de montant
01 -Machinerie et travaux	9 462 578,60 $
02 -Marine et portuaire	2 075 130,21 $
03 -Hydroélectrique	1 496 395,65 $
20% MARINE	1 405 415,56 $
04 -Minier	998 681,01 $
08 -Autres	462 184,81 $
10 - Equipement golf	75 892,44 $

Re: Switch category

Anonymous — Wed, 24 Jul 2024 12:04:42 GMT

Hi sportive92003 ,

BeaBF , thanks for your concern about this case.

Based on your description, I added a client column:

Here's the original pie chart:

Please follow these steps:

1.Create a measure and filter the sector:

Measure = IF(SELECTEDVALUE('Table'[sector])="20% MARINE, 20% HYDRO",1,0 )

2.Create a calculated column:

Column = var A=CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[sector]="20% MARINE, 20% HYDRO")) RETURN IF(LEFT('Table'[sector],2) in {"01","02"},CALCULATE(SUM('Table'[amount])+A*1/3,ALLEXCEPT('Table','Table'[sector])),CALCULATE(SUM('Table'[amount]),ALLEXCEPT('Table','Table'[sector])))/2

3.The final result is as follows:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Switch category

sportive92003 — Wed, 24 Jul 2024 12:57:15 GMT

Hello,

See results for my client X (name is BNAC). I should see the 3 sectors but nothing appears

Re: Switch category

sportive92003 — Thu, 25 Jul 2024 11:26:01 GMT

Hello dear, thanks for your help, I progress well! I'm near from the solution. I have added the amount of sector 1,2 and 3 to the 1/3 of amount of sector 20%. Now I just need to show the VAR A_BNAC; B_BNAC and C_BNAC and the rest of the others sectors (normal amount). What would be the next step afetr RETURN?

Secteur_BNAC =

VAR A = CALCULATE([Montant Measure],Fusionner1[Clients.Secteur ] = "01 -Machinerie et travaux")

VAR B = CALCULATE([Montant Measure],Fusionner1[Clients.Secteur ] = "02 -Marine et portuaire")

VAR C = CALCULATE([Montant Measure],Fusionner1[Clients.Secteur ] = "03 -Hydroélectrique")

VAR Montant_BNAC = IF('Fusionner1'[Secteur 20%] = 1, 'Fusionner1'[montant] / 3, BLANK() )

VAR A_BNAC = (A+Montant_BNAC)

VAR B_BNAC = (B+Montant_BNAC)

VAR C_BNAC = (C+Montant_BNAC)

RETURN

Re: Switch category

BeaBF — Thu, 25 Jul 2024 11:54:15 GMT

sportive92003 here:

Secteur_BNAC =

VAR A = CALCULATE([Montant Measure], Fusionner1[Clients.Secteur] = "01 -Machinerie et travaux")

VAR B = CALCULATE([Montant Measure], Fusionner1[Clients.Secteur] = "02 -Marine et portuaire")

VAR C = CALCULATE([Montant Measure], Fusionner1[Clients.Secteur] = "03 -Hydroélectrique")

VAR Montant_BNAC = IF(Fusionner1[Secteur 20%] = 1, Fusionner1[montant] / 3, BLANK())

VAR A_BNAC = (A + Montant_BNAC)

VAR B_BNAC = (B + Montant_BNAC)

VAR C_BNAC = (C + Montant_BNAC)

RETURN

SWITCH(

TRUE,

Fusionner1[Clients.Secteur] = "01 -Machinerie et travaux", A_BNAC,

Fusionner1[Clients.Secteur] = "02 -Marine et portuaire", B_BNAC,

Fusionner1[Clients.Secteur] = "03 -Hydroélectrique", C_BNAC,

Fusionner1[Secteur 20%] = 1, Montant_BNAC,

Fusionner1[montant])

BBF

Re: Switch category

sportive92003 — Thu, 25 Jul 2024 13:30:04 GMT

Hello,

I'm sorry, the result is same as usual.

Result sector 01 should be 9 931 050 (sector 01= 9 462 578 + 1/3 of sector 20% (468 471)

Re: Switch category

sportive92003 — Thu, 25 Jul 2024 14:47:12 GMT

Hello again, it's ok when using de RETURN A_BNAC, but adding the switch it come back to the original version. Selecting sector 01, it should appears sector 01 and sector 20% (1/3) like that. In the programmation something is wrong with the switch. Do you have any other solution?