Skip to main content
cancel
Showing results for 
Search instead 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

Reply
zy910830
Frequent Visitor

Calculate dynamic MIN/MAX of SUM

I have a simple dataset like below:

typemonthyearvalue
AJan201720
AFeb20172
AMar201714
AJan20189
AFeb201830
AMar20183
AJan201917
AFeb201915
AMar201930
BJan20179
BFeb20179
BMar201719
BJan201824
BFeb201817
BMar201817
BJan201920
BFeb20199
BMar201919
CJan201726
CFeb201724
CMar201728
CJan201829
CFeb201810
CMar201818
CJan201929
CFeb201916
CMar201914

 

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:

zy910830_1-1675334488574.png

 

 

Thanks very much in advance!

 

 

 

2 REPLIES 2
zy910830
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?

zy910830_0-1675341372224.png

 

zy910830
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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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