Forum Discussion

Maricla's avatar
Maricla
Frequent Visitor
2 years ago
Solved

Measure to calculate sum by groups

I have one year of monthly data for 14 Locations in a DataSet that has 4 columns: i) Location_ID; ii) FloorArea in squared meters; iii) MonthYear; and iv) Amount.

 

Some sites do not have a full year of data (see yellow rows in DataSet and SiteMonthCount in tables with measures). And this is why I am having a problem in calculating my measure.

 

An extract of the DataSet looks like this:


My aim is to calculate the ratio between Amount and FloorArea (£/sqm). However, I am struggling to calculate a measure for the floor area that gives me the right total.

 

The total FloorArea should be 124,162 sqm. 
How to build a measure for it, considering we have floor area for each monthly row of data and that not all locations have a full year?

The closest I get is 106,735 with

FloorArea_M1 = SUM('DataSet'[FloorArea])/COUNT('DataSet'[SiteId])*DISTINCTCOUNT('DataSet'[SiteId])

 

 

 

  • ajohnso2's avatar
    ajohnso2
    2 years ago

    Hi I would go back to power query and re model your data.

     

    For example, i would create a dimension table which holds 1 single value per location id, you would only need 2 columns (Location ID & Floor Area) once this table is created you can join it to your fact data on location id.

     

    This will allow you to accurately calculate the floor area.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Maricla

     

    For your question, here is the method I provided:

     

    "DataSet"

     

    Create measures.

     

    SiteMonthCount = 
CALCULATE(
    COUNTROWS('DataSet'), 
    FILTER(
        ALL('DataSet'), 
        'DataSet'[Location_ID] = MAX('DataSet'[Location_ID])
    )
)

     

     

    FloorArea_M1 = 
CALCULATE(
    AVERAGE('DataSet'[FloorArea]), 
    FILTER(
        ALL('DataSet'), 
        'DataSet'[Location_ID] = MAX('DataSet'[Location_ID])
    )
)

     

     

    Ratio_M1 = 
CALCULATE(
    SUM('DataSet'[Amount]),
    FILTER(
        ALL('DataSet'),
        'DataSet'[Location_ID] = MAX('DataSet'[Location_ID])
    )
)
/
'DataSet'[FloorArea_M1]

     

     

    Here is the result.

     

     

    If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

     

    Regards,

    Nono Chen

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

     

  • ajohnso2's avatar
    ajohnso2
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    Hi I would go back to power query and re model your data.

     

    For example, i would create a dimension table which holds 1 single value per location id, you would only need 2 columns (Location ID & Floor Area) once this table is created you can join it to your fact data on location id.

     

    This will allow you to accurately calculate the floor area.

    • Maricla's avatar
      Maricla
      Frequent Visitor
      2 years ago

      Thanks for your fresh look at things. It's working very well now.