Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculated column ..**Its urgent PLz****

Hi All, 

 

Below is my table with data 

ProductTypeSales
penA7
PenA9
PenB7
PenB4
PenB10
PenB5
PenB8
PenC4
PenC4
RubberA2
RubberB2
RubberE4
RubberD4
RubberE10
ScaleA10
ScaleA4
ScaleA7
ScaleA3
ScaleB1
ScaleB7
ScaleC1
ScaleC1
ScaleD4
ScaleE8
ScaleE5

 

Output 

TypeObsRuledPinned
A826
B6.8  
C   
D   
E   

 

need to calcualte the avg for each product and type .. In Obs we calculate for pen , ruled for rubber and pinned for scale . i need this to be displayed in table  not matrix table. 

is there any possible to get the dax as calculated column rather than measure . 

for ex:

in the obs for type A 

there are 2 pens for type A ,so the avg is 8 .

Need Help

5 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Create a table as follows

    TableAv = SUMMARIZECOLUMNS(TableXX[Type], "Obs", CALCULATE(AVERAGE(TableXX[Sales]), TableXX[Product] = "Pen") ,
                                          "Ruled" , CALCULATE(AVERAGE(TableXX[Sales]), TableXX[Product] = "Rubber"),
                                          "Pinned", CALCULATE(AVERAGE(TableXX[Sales]), TableXX[Product] = "Scale"))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thnks for ur reply ..

       

      i have a filter with pen,rubber,scale .. so when i select the pen .. in the table only pen dax should excute or come . so when i select rubber & pen , in the table both rubber and pen data should come in column .. 

      can you help out with this .. 

      • V-lianl-msft's avatar
        V-lianl-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        According to your description, what you want to achieve is to dynamically add or delete columns according to slicer.

        After the table is created according to HotChilli  said before, you can refer to using-a-slicer-to-show-different-measures.

         

        Best Regards,
        Liang
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Razwan's avatar
    Razwan
    Icon for Helper I rankHelper I
    6 years ago

    Anonymous 
    Hi,
    Why do you need a calculated column if a measure is more suitable in this situation?
    Also, do you already have a table with distinct Type values or you need to create one like the Output?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    This might help, write following DAX measures:

    Amount=SUM([Values])

    NumOfDistinctDates=DISTINCTCOUNT([Date])

    Avg=DIVIDE([Amount],[NumOfDistinctDates])