cancel
Showing results for
Did you mean:
Regular Visitor

Summed Max Across Columns

Hi,

I'm having a little bit of trouble getting a particular calculation into one of my Power BI queries and was hoping one of you may be able to help!

I have a table that lists the number of cars we have in a dealership month on month. The data looks something like this:

 Dealer Car Jan Feb Mar Dealer ABC Car Type 1 5 4 1 Dealer ABC Car Type 2 4 9 3 Dealer ABC Car Type 3 1 7 8 Dealer XYZ Car Type 1 2 2 3 Dealer XYZ Car Type 2 1 4 1 Dealer XYZ Car Type 3 4 0 2

What I want to do is to find out the maximum for each dealer as a whole. If I enter a Max calculation into a new column and display it in a visual table in a report, I am getting a skewed figure as, in the example for Dealer ABC, it takes the 5 from car 1, 9 from car 2 and 8 from car 3 and gives me 22.

What I actually want to see is that for that dealer, the figures are Jan = 10, Feb = 20, Mar = 12, making the max 20.

I'm tying myself in knots trying to figure this out and I'm sure there's a simple way of doing it.....I just don't know what that is!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Member

Re: Summed Max Across Columns

Hello @KatieH

At first, you need to transpose your table (you can use Power Query) like this:

Next, create measure something like this:

Max Sales Accross Month :=
MAXX (
SUMMARIZE ( 'Table'; 'Table'[Month] );
"MonthSales"; CALCULATE ( SUM ( 'Table'[Quantity] ) )
);
[MonthSales]
)

Established Member

Re: Summed Max Across Columns

Hi @KatieH,

I created the measure but with a slighty different approach where my table is called Data, my fields named Dealer, Amount and Mois( for Month).

Feel free to check both solutions and let us know:

```Final =
CALCULATE (
MAXX (
SUMMARIZE (
Data,
Data[Dealer],
Data[Mois],
"Sales", SUM(Data[Amount] )
),
[Sales]
),
ALLEXCEPT(Data,Data[Dealer])
)```

Ninter

3 REPLIES 3
Member

Re: Summed Max Across Columns

Hello @KatieH

At first, you need to transpose your table (you can use Power Query) like this:

Next, create measure something like this:

Max Sales Accross Month :=
MAXX (
SUMMARIZE ( 'Table'; 'Table'[Month] );
"MonthSales"; CALCULATE ( SUM ( 'Table'[Quantity] ) )
);
[MonthSales]
)

Established Member

Re: Summed Max Across Columns

Hi @KatieH,

I created the measure but with a slighty different approach where my table is called Data, my fields named Dealer, Amount and Mois( for Month).

Feel free to check both solutions and let us know:

```Final =
CALCULATE (
MAXX (
SUMMARIZE (
Data,
Data[Dealer],
Data[Mois],
"Sales", SUM(Data[Amount] )
),
[Sales]
),
ALLEXCEPT(Data,Data[Dealer])
)```

Ninter

Regular Visitor

Re: Summed Max Across Columns

Thank you both so much! I tried both of these and they worked brilliantly!