Forum Discussion

vedantsri's avatar
vedantsri
Icon for Helper I rankHelper I
3 years ago

How to apply a different measure based on column value?

Hi All,

 

I have a table that looks like this:

OfficeTier
AHigh
BMid
CMid
DLow

 

Office column is a distinct column.

 

Now I wanted to calculate Demand (Which is coming from a separate demand table) for each of these offices.

The demand would be calculated diffeently based on the 'Tier' of the office.

 

So now I have 3 measure that I created to calculate the demand for each office depending on the value of the Tier column in the office table mentioned above (One measure for each Tier type; High, Mid, Low). I also have a slicer for the 'Office' column in my Office table for a user to select the office they want the demand for. If multiple offices are selected then we would get the sum of the calculated demand for each of those offices.

 

I wanted to create a 4th measure that I would use in a visual that is able to display the demand of an office or the sum of demand of multiple selected offices. This measure however would dynamically calculate the value of the demand (using my 3 created measures) based on the 'Tier' value in the office table mentioned above. How should I do that?

 

Thanks,

Vedant

Need Help

6 Replies

  • KNP's avatar
    KNP
    Icon for Super User rankSuper User
    3 years ago

    Could you post your existing measures and if possible, a screen shot of what the visuals currently look like?

     

    • vedantsri's avatar
      vedantsri
      Icon for Helper I rankHelper I
      3 years ago

      Hi KNP,

       

      Thank you for responding!

      The three measures I created are similar to this one:

      HighTierDemandNeeded =
       VAR DemandDeficit = [Supply]>2*[Demand]
      var tier2 = CALCULATE([Supply]-2*[Demand])
      return
      if(DemandDeficit,tier2,0)
       
      Similarly, 
      MidTierDemand =
       VAR DemandDeficit = [Supply]>1*[Demand]
      var tier2 = CALCULATE([Supply]-1*[Demand])
      return
      if(DemandDeficit,tier2,0)
       
      and so on for Low tier. 
       
      The [Demand] measure that I have used in these measures is just a simple summation measure on a Column from a separate Demand table that I have connected to my Office table using the Office column. 
       
      So now I want a visual that shows the right measure value (between hightierDemand, midtierdemand, lowtierdemand) based on the 'Tier' of the office selected in my slicer.
      • KNP's avatar
        KNP
        Icon for Super User rankSuper User
        3 years ago

        I think I have an understanding of what you're trying to do, just need a little more info.

         

        What is the [Supply] measure?

        It would help to have some sample data from the demand table if possible.