Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.