Forum Discussion

MigWare's avatar
MigWare
Icon for Helper I rankHelper I
6 years ago
Solved

Aggregate and calculate two different columns

Hi,

 

I´ve two columns, material type and sales data. I need to show all the sales data that falls under material type X, Z, Y and material type A, B, C and R and so on...... basically i need to aggregate the sales data by diferent material types. How can this be achieved.

 

Tks,

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    You can easily add other new custom category to the original formula by IF function, like this:

    New_Category =
IF (
    'Table'[Category]
        IN { "Hi-Energy GNA 98", "SPB95 Hi-Energy", "SPB98 Hi-Energy" },
    "Petrol",
    IF ( 'Table'[Category] IN { "A", "B", "C" }, "Gas", 'Table'[Category] )
)

     

    Best Regards,

    Giotto

12 Replies

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

    Question is not clear can you share some example scenario like showing table etc

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

      Material

      		Sales
      A1000
      B5000
      C6000
      D9000
      E3000
      F7000
      G4000
      A4000
      B5999
      C3534
      D4324
      E525
      F5253
      G5235
      A23432
    • MigWare's avatar
      MigWare
      Icon for Helper I rankHelper I
      6 years ago

      i want to aggregate ABCD under one single name, like Shirts, and show the sum of those sales number

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

    Hi MigWare 

    based on your poor requirement I see 2 measures as asolution:

    Measure = CALCULATE(SUM(Table[sales]), ALLEXCEPT(Table, Table[Material]))

    or

    Measure = CALCULATE(SUM(Table[sales]), FILTER(ALL(Table), Table[Material] IN {"X", "Z", "Y"} ))

     

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

      Hi az38 ,

       

      Thanks for the input, however i might not have totally clear on the objective. I want to aggregate HI Energy GNA98, SPB95 Hi-Energy and SPB98 HI Energy into one category, like Petrol and show the sum of the values of all those products under one single entity ( Petrol). So I have the date on one axis, the sales on the other and the product description on the legend