The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Everyone,
Hope you can help me with this problem I´ve been working on for a couple of days without success. I have made a dummy-table to represent the data. I have these fields Customer, Volume, Direction, Corridor, Date and Port. I want to get the maximum Volume per Year, month and Customer. For example if Customer1 had a volume in January of 100,125, 200 and 150 in years 2020, 2021, 2022 and 2023, respectively, the final value should be 200 and so on for the rest of the months and the grand total should be the sum of those max values
I have used a quick measure to calculate the max values per year, then I use in my visualization the other fields to see how it should look with those constraints, but when I download the data, the aggregations seem off and the values are not the same as in PowerBI.
I couldn't attach the files as I'm a new user:
https://drive.google.com/drive/folders/1uSsfUd1d1Ow-RbZ2GYR3tJC-j7ipwRdv?usp=sharing
Thanks!!
Solved! Go to Solution.
Volume max across years =
SUMX (
ADDCOLUMNS (
VALUES ( 'Calendar'[Month] ),
"MY",
MAXX (
ADDCOLUMNS (
ALLSELECTED ( 'Calendar'[Year] ),
"s", CALCULATE ( SUM ( Sheet1[Volume] ) )
),
[s]
)
),
[MY]
)
lbenlin thank you for your advice and solution, it's almost what I wanted, I'd like the total "Max Vol" to be the sum of the Monthly "Max Vol" values.
In this example instead of getting 27667 at max volume, it should be the sum of the monthly results (30756)
Volume max across years =
SUMX (
ADDCOLUMNS (
VALUES ( 'Calendar'[Month] ),
"MY",
MAXX (
ADDCOLUMNS (
ALLSELECTED ( 'Calendar'[Year] ),
"s", CALCULATE ( SUM ( Sheet1[Volume] ) )
),
[s]
)
),
[MY]
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |