cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculate dynamic MIN/MAX of SUM

I have a simple dataset like below:

 type month year value A Jan 2017 20 A Feb 2017 2 A Mar 2017 14 A Jan 2018 9 A Feb 2018 30 A Mar 2018 3 A Jan 2019 17 A Feb 2019 15 A Mar 2019 30 B Jan 2017 9 B Feb 2017 9 B Mar 2017 19 B Jan 2018 24 B Feb 2018 17 B Mar 2018 17 B Jan 2019 20 B Feb 2019 9 B Mar 2019 19 C Jan 2017 26 C Feb 2017 24 C Mar 2017 28 C Jan 2018 29 C Feb 2018 10 C Mar 2018 18 C Jan 2019 29 C Feb 2019 16 C Mar 2019 14

Goal is to create two MIN/MAX measures that capture the MIN/MAX of sum of any combination (determined by slicer) of the types (A, B and C) for each month across the years so that i can create a seasonal chart like below (sum of A & B), with the MIN/MAX measures set as Upper bound & Lower bound in Error Bars:

2 REPLIES 2
Frequent Visitor

New question arises.... how do I hide e.g. 2017 from legends and yet still display the same MIN/MAX like the below?

Frequent Visitor
`sum_value = CALCULATE ( SUM( table[value] ), ALLSELECTED( table[type] ) )`
`max_of_sum = MAXX( ALLSELECTED( table[type], table[year] ), table[sum_value] )`
`min_of_sum = MINX( ALLSELECTED( table[type], table[year] ), table[sum_value] )`

Have played around a bit, the above seems to work - would love some confirmation here?

Thanks vm

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors