Forum Discussion

sportive92003's avatar
sportive92003
Helper 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...
BeaBF's avatar
BeaBF
Super User
2 years ago

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

 

BBF

sportive92003's avatar
sportive92003
Helper II
2 years ago

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 travaux9 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 $

 

  • 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
      Helper II
      2 years ago

      Hello,

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

       

       

       

    • sportive92003's avatar
      sportive92003
      Helper 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
        Super 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