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 ag...
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

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

Hi,

 

That option is not available, greyed out. Any other ideia?

 

 

v-gizhi-msft's avatar
v-gizhi-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi,

 

Please try to create this calculated column:

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

Then choose this new category column as Legend field, the result shows:

See my attached pbix file.

 

Best Regards,

Giotto

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

    Hi,

     

    Thanks for providing that solution, however when I change the table fields to match my data I´m getting the following error:

     

    A single value for column 'Product Description' in table 'Master' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

    • v-gizhi-msft's avatar
      v-gizhi-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi,

       

      Please try to create a calculated column rather than a measure.

      Please check my previous reply again.

      Thanks!

       

      Best Regards,

      Giotto

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

        Hi v-gizhi-msft,

         

        Thanks, I had already figured that out and it worked however I´m just able to create one aggregation. So currently i´m aggregating 3 itens into one "Petrol" but i would need to aggregate the other 3 into another category "Gas". How is that possible?

         

        Regards,