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

[natabird3]

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. 

[Vvelarde]

@natabird3 

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 (
            ADDCOLUMNS (
                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] )

I hope that this help you

Regards

Victor

[natabird3]

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

[Vvelarde · Accepted solution]

@natabird3 

Hi, don't exactly why the differences.

i sent the link of the pbix file

https://drive.google.com/file/d/1Ojz6AxGgUpilt7yVTvYwJbRuqR2ektcP/view?usp=sharing

Regards

Victor

[natabird3]

@Vvelarde  

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?

[edhans]

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 get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[natabird3]

Thanks for the quick reply, @edhans please try the following link:

https://we.tl/t-GQNs5buFOT

[edhans]

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

[natabird3]

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

[edhans]

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(
        ADDCOLUMNS(
            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.