Aggregate data at granular level and assign it to high level

[Nawaz]

I'm a new PowerBI user, so please excuse if this is silly question

I want to get the highest sales for each category, and at the same time i don't want to show subcategory in the view.

In Tableau this can be achieved using the mix of Include and Fixed Level of Detail functions. Is there something similar in Power BI ?

Sample Data: 

Category	Subcategory	Sales
Electronics	LaptopA	325
Electronics	LaptopB	200
Electronics	LaptopC	110
Furniture	Chairs	30
Furniture	Chairs	50

Exected Output:

Category	Sales
Electronics	350
Furniture	50

Thanks,

Nawaz

[Nawaz]

Hi Tom, thanks for the response. I aplogize for not providing the enough data to get the desired result.

Bascially, i would like to calculate the sales at sub category level and assign the highest sales to catergoy without mentioning the sub category in the view as shown below in expected output.

Thanks,

Nawaz

[v-junyant-msft · Accepted solution]

Hi @Nawaz ,

@TomMartens Thanks for your concern about this case!

And @Nawaz , you can use these DAXs to achieve this:

_SUM = 
CALCULATE(
    SUM('Table'[Sales]),
    ALLEXCEPT('Table', 'Table'[Sub-Category])
)

_MAX = MAXX('Table', [_SUM])

Then put Category and _MAX into the table visual:

Or if you can use Power Query, you can also achieve this by using Group By twice:
First:

Then:

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

[TomMartens]

Hey @Nawaz ,

create a table visual, add the category and the sales column, finally change the aggregation of Sales column from Sum to Maximum:

Hopefully, this provides what you are looking for.

Regards,

Tom