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 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!

 

8 Replies

  • 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!