Forum Discussion

eriweste's avatar
eriweste
Icon for Helper I rankHelper I
4 years ago

Groupby, SUM based on interval

Hi all,

Need help to visualize compensation amounts based on fixed values and amount of consumptions

 

Data

We have a table containing consumtions values (numeric, decimal)

 

Fixed values table

ConsumptionInterval,compensation

7000-7999,100

8000-8999,200

9000-9999,300

 

This needs to be "connected" to the dataset that contain the consumption and present a aggregated amount of compensation sum per interval

 

 

Ex:

How much compensastion should be payed to accounts in the different intervals

 

Note that only specific months are in scoop (in ex above december)

 

Any ideas ?

 

Br

erik

Need Help

5 Replies

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

    eriweste,

     

    This solution requires a slight modification to your fixed values table (ConsumptionInterval). See below:

     

     

    There is no relationship between the tables. Create the measure below:

     

    Compensation Total =
SUMX (
    FactTable,
    VAR vConsumption = FactTable[Consumption]
    RETURN
        CALCULATE (
            MAX ( ConsumptionInterval[Compensation] ),
            vConsumption >= ConsumptionInterval[Consumption Interval Start],
            vConsumption <= ConsumptionInterval[Consumption Interval End]
        )
)

     

     

    • eriweste's avatar
      eriweste
      Icon for Helper I rankHelper I
      4 years ago

      Hi, thanks for the reply and sorry for the delay in response.

       

      The formula works good, but I discovered an additional issue 

      More info:

      The consumtion column get summarized in my table, some "Instnum" consist of multiple rows within the same date range (ex Month = Dec)

       

      This creates the sumx formel to be used twice (2)

      This customer hits the Interval range twice

       

       

      Above screenprint have the consumption summarized (total) and thats what the compensation should be based upon.

       

      ANy further ideas ?

      Br

      erik

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi eriweste ,

     

    Does DataInsights 's reply solve you problem?

    If the problem is solved, please mark the helpful reply as the solution, more people will benfit.

    If not, please provide the expected result.

    I'm wondering if you want the accumulated value, for example if Consumption is 8500, your result is 100+200=300

     

     

    Best Regards,

    Stephen Tao

     

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