Aggregate and calculate two different columns

[MigWare]

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,

[az38]

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]

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

[az38]

Hi @MigWare 

see this technique

https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning

[MigWare]

Hi,

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

[v-gizhi-msft]

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]

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]

Hi,

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

Please check my previous reply again.

Thanks!

Best Regards,

Giotto

[MigWare]

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,

[v-gizhi-msft · Accepted solution]

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

[VijayP]

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

[MigWare]

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

[MigWare]

Material	Sales
A	1000
B	5000
C	6000
D	9000
E	3000
F	7000
G	4000
A	4000
B	5999
C	3534
D	4324
E	525
F	5253
G	5235
A	23432