cancel
Showing results for
Did you mean:
Continued Contributor

## Max per brand and country and average per category for selected period

Dear all,

I am having a rather complicated maybe question:

I have three levels of how the data would be displayed in a pivot table:

 Jan Feb ... Category - Beverages 11000 12000 ... Country - USA 6000 10000 ... Brand 1 1000 5000 ... Brand 2 2000 4000 ... Brand 3 3000 1000 ... Country - China 5000 2000 ... Brand 1 5000 2000

So if you see the table above what i would need to create a measure for is, the max for each brand in each country, and then do the sum of all countries, and finally do an average of that for the period selected.

So in the example above, the output i should get ideally with this measure should be as follows:

In USA we had max values for Brand 3 in Jan (3000) and Brand 1 in Feb (5000), while in Chine the max were in Jan (5000) and in Feb (2000). The sum for all countries would have been 8000 in Jan and 7000 in Feb. And the average would have then be (8000+7000)/2 months = 7500 - the result i am trying to get.

Any help would be greatly appreciated, thanks and stay safe.

1 ACCEPTED SOLUTION
Community Champion

Hi, don't exactly why the differences.

i sent the link of the pbix file

Regards

Victor

Lima - Peru
9 REPLIES 9
Community Champion

Hi, i think this could give you some lights about your problem

1. Create a measure :

``````MaxByCountry =
IF (
HASONEVALUE ( Sheet1[Country] );
MAXX ( VALUES ( Sheet1[Brand] ); CALCULATE ( SUM ( Sheet1[Sales] ) ) );
SUMX (
SUMMARIZE (
Sheet1;
Sheet1[Category];
Sheet1[Country];
Sheet1[Date];
"VALUETOSUM"; MAXX ( VALUES ( Sheet1[Brand] ); CALCULATE ( SUM ( Sheet1[Sales] ) ) )
);
[VALUETOSUM]
)
)``````

Note: If you had a Month Column replace instead of Sheet Date.

2. Another Measure to count the months:

``````CountMonths =
COUNTROWS (
DISTINCT (
SELECTCOLUMNS (
FILTER ( Sheet1; NOT ( ISBLANK ( Sheet1[Date] ) ) );
"MonthN"; MONTH ( Sheet1[Date] )
);
"MonthS"; [MonthN]
)
)
)``````

Notes: I use the Not Is Blank because your Sheet had blank rows in the Date Column.

If you add a column with the Month in the table this Count Formula will be easier.

3. The measure needed:

``````Result =
DIVIDE ( [MaxByCountry]; [CountMonths] )``````

Regards

Victor

Lima - Peru
Continued Contributor

@Vvelarde i tried your solution, however it doesn't seem to give me the right value.

When i take for instance Italy, i should get the following result: 10,666,863.

Maybe i confused something but i am trying to get the result for each country and not for the category if that makes sense. (but also for the category the right value should be:

Amazon: average 15,203,598 and sum 182,443,175

eBay: average 6,447,745 and sum: 77,372,943

I am not sure why we have some differences.

Community Champion

Hi, don't exactly why the differences.

i sent the link of the pbix file

Regards

Victor

Lima - Peru
Continued Contributor

I dont know if it helps, but what i am trying to do is get the Max for the country, of one category in particular. So if i can create a filter for just one category and get the max for each country for a period that would solve what i am trying to get i believe. Each brand within the category have a unique sku which is only within the country so i think it should work. What do you think?

Super User

If you could provide the sample data its original table format that would be helpful. Then we can construct the necessary measures to get what you need.

How to provide sample data in the Power BI Forum

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Continued Contributor

https://we.tl/t-GQNs5buFOT

Super User

Hrm... getting max of brand per period is pretty simple, and for all periods, but I'm struggling to figure out how to get the max for each period then add them up. I'll get back to it later when I have more time, but perhaps @Greg_Deckler would be able to do it if he is available today.

my work in progress is attached. The grand total column is wrong as that is the max across months, not the additive months, which should be about \$26M. (I didn't use all of your data, just the first 500 rows or so.)

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Continued Contributor

thanks for trying, i am sure it wont be an easy one to solve 😞

Super User

Well, I played with this during lunch. Tantalizingly close I feel.

``````Max for Brand 2 =
VAR BrandMax =
MAXX(
ALL(
Sales[Brand],
Sales[Sales]
),
[Total Sales]
)
RETURN
SUMX(
GROUPBY(
Sales,
'Date'[Month Year]
),
"Monthly Total", BrandMax
),
[Monthly Total]
)``````

THis returns the 2nd column in the red box:

The monthly totals are right. The problem is the base MAX formula (first column) returns 19,800,392. This is the max of the highest month - April.

In the 2nd column the total shown of \$158M is the \$19.8M * 9, so it returns the same value each month and adds them up. I couldn't get it to cycle through each month and add up to the right total of \$118.7M for those monthly amounts. I'll play with more after work this evening.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors