Forum Discussion

shaun-bi's avatar
shaun-bi
New Member
4 years ago
Solved

Help declaring max value measure for a guage?

I have a single dataset that has table called "reservations", which is presented in a BI report as a simple table. In this table it has a column called "buildings". Each building has a maximum capacity number (not contained in the table) which is hard set and can be included in the measure (if that makes sense to).

 

I want a measure to count the rows for each building in the table i.e London so that when a slice is set in the report to filter the table by building, and to define a maximum value for the guage which is building specific.

 

We're just using a simple measure to count the rows displayed in the table today which is applied to the guage:

Measure1 = COUNTROWS(reservations)

  • v-robertq-msft's avatar
    v-robertq-msft
    4 years ago

    Hi, 

    According to your description, I can clearly understand your requirement, you want to create a gauge chart to display the comparison between your maximum capacity number and current capacity number, right? I think you can achieve your requirement by creating some measures like this:

    This is the test data I created based on your description:

     

    maximum capacity number =

MAX('reservations'[capacity number])
    Current capacity numbers = COUNT(reservations[buildings])
    Max = maxX(ALL(reservations),[capacity number])
    Min = 0

     

    And you can create a gauge chart and place it like this, then you can get what you want, like this:

     

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    shaun-bi Maybe:

    Max Measure = 
  VAR __Building = MAX('Table'[Building])
RETURN
  SWITCH(__Building,
    "Building1",44,
    "Building2",33,
    ...
  )
    • shaun-bi's avatar
      shaun-bi
      New Member
      4 years ago

      Thanks, this sets the maximums perfectly.

       

      How do I combine that with the countrows to compare the table data to the maximum value. As right now, the gauge is just displaying the maximum values.

       

      I've used BI for 2 hours only so sorry for the newbie questions!

       

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    shaun-bi 

    Do you need a new measure to get the capacity number based on the currently filtered building ? How about a new measure?

     

    MaxValue = SELECTEDVALUE (reservations[capacity number]) , 0 )

     

    or

    MaxValue = MAX(reservations[capacity number])



  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, 

    According to your description, I can clearly understand your requirement, you want to create a gauge chart to display the comparison between your maximum capacity number and current capacity number, right? I think you can achieve your requirement by creating some measures like this:

    This is the test data I created based on your description:

     

    maximum capacity number =

MAX('reservations'[capacity number])
    Current capacity numbers = COUNT(reservations[buildings])
    Max = maxX(ALL(reservations),[capacity number])
    Min = 0

     

    And you can create a gauge chart and place it like this, then you can get what you want, like this:

     

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.