Forum Discussion

Gchandrukris's avatar
Gchandrukris
Helper I
6 years ago

additional column in matrix

Hi 

I am new to powerbi , I wanted to add additional column(Profit) in the matrix . Please refer the table below.

 

Can the expected below is possible in Matrix ?

 

Table 

 

CustomeidProductDecileProfit 
1p1d01100 
2p1d01200 
3p1d02300 
4p1d02400 
5p1d03500 
11p2d011000 
12p2d012000 
13p2d023000 
14p2d024000 
15p2d035000 
Expected
productprofitdo1do2do3
p11500221
p215000221
Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello!!

    First create this measures

     

    Profit_M = sum(Hoja5[Profit])

d01 = CALCULATE(COUNT(Hoja5[Product]);Hoja5[Decile] = "d01")

d02 = CALCULATE(COUNT(Hoja5[Product]);Hoja5[Decile] = "d02")

d03 = CALCULATE(COUNT(Hoja5[Product]);Hoja5[Decile] = "d03")

     

    Finally, put all together in a matrix.

     

    Regards!!

     

    • Gchandrukris's avatar
      Gchandrukris
      Helper I
      6 years ago

      Thanks for your response. Is there any other way other than creating measures. Because Deciles are dynamic. it keeps increasing.

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        6 years ago

        Hi Gchandrukris ,

         

        Create a new table,set"profit","d01","d2","d03" as the row value of the column:

        Then create a measure as below:

         

         

        Measure = 
SWITCH(SELECTEDVALUE('Table (2)'[Column]),"profit",SUM('Table'[Profit]),"do1",CALCULATE(COUNT('Table'[Product]),'Table'[Decile]= "d01"),"do2",CALCULATE(COUNT('Table'[Product]),'Table'[Decile]= "d02"),
"do3",CALCULATE(COUNT('Table'[Product]),'Table'[Decile]= "d03"),BLANK())

         

         

        Finally you will see:

         

        For the related .pbix file, pls click here.

         

        In my suggestion,only one measure is needed ,I'm not sure whether it is what you need?

         

         

         

        Best Regards,
        Kelly
         
        Did I answer your question? Mark my post as a solution!
  • ahmedoye's avatar
    ahmedoye
    Responsive Resident
    6 years ago

    Gchandrukris , you can also break down your mesures into small little components this way:


    Profits = SUM('Table'[Profit])

    Decile Count = COUNT('Table'[Decile])
    d0 1 = CALCULATE([Decile Count],'Table'[Decile] = "d01")
    d0 2 = CALCULATE([Decile Count],'Table'[Decile] = "d02")
    d0 3 = CALCULATE([Decile Count],'Table'[Decile] = "d03")
     
    You can then add Product Field and the Measures Profits, d01, d02 and d03 to your Matrix.

    If this solution works for you, kindly kudo my response and mark as solution to allow others benefit from it.
     
     

     

    • Gchandrukris's avatar
      Gchandrukris
      Helper I
      6 years ago

      Hi,

      As you mentioned, I have created a separate measures for each column which works fine.

       

      In my case my measures calculates sales / sum(sales) for all columns. I wanted to show in the tooltip sales & totalsales.

       

      eg

      category  Measure1  Measure2 Measure3

      A              10             20             30

      B              20             30             30

       

      after calculating the percentage

      category  Measure1  Measure2 Measure3

      A             17 %           33%          50%

      B              25%            37.5%          37.5%

       

       

      In my tool on 17% I wanted to show s

      Sales: 10

      TotalSales : 60

       

      can you help me on this