cancel
Showing results for
Search instead for
Did you mean:
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:

Thanks very much in advance!

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

## Helpful resources

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