Forum Discussion

sportive92003's avatar
sportive92003
Icon for Helper II rankHelper II
2 years ago

Switch category

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 gr...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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!

 

 

 

 

sportive92003's avatar
sportive92003
Icon for Helper II rankHelper II
2 years ago

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
  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    2 years ago

    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

    • sportive92003's avatar
      sportive92003
      Icon for Helper II rankHelper II
      2 years ago

      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)

       

    • sportive92003's avatar
      sportive92003
      Icon for Helper II rankHelper II
      2 years ago

      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?