I am having a rather complicated maybe question:
I have three levels of how the data would be displayed in a pivot table:
|Category - Beverages||11000||12000||...|
|Country - USA||6000||10000||...|
|Country - China||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.
Solved! Go to Solution.
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
@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.
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?
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.)
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.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
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!
Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.