Keep measure calculating on lowest level

[aspmag]

Hi,

I've struggled with this logic for quite some time and not been able to find a solution myself or on the forum, hope somebody can assist me. 

My data model is more complex then I picture in this post but I've simplified it for the context of this question. 

I have a table containing hours spent producing a certaint quantity of a product in every month, something like this:

Year	Month	Start of Month	ProductNo	Hours	Quantity
2021	1	01.01.2021	1	250	500
2021	2	01.02.2021	1	240	520
2021	3	01.02.2023	1	246	550
2021	1	01.01.2021	2	600	450
2021	2	01.02.2021	2	630	520
2021	3	01.02.2023	2	630	450

I want to use the hours spent per product produced in a given month as a reference for the remaining months in that year, meaning Hours / Quantity. Simple enough, I've done this through a measure:

Hours ratio = 
VAR _Year = SELECTEDVALUE(Dim_Calendar[Date].[Year])

RETURN

CALCULATE(
    DIVIDE(SUM(Hours), SUM(Quantity)),
    FILTER(
        ALL(Dim_Calendar),
        Dim_Calendar[Date].[Year] = _Year && 
        Dim_Calendar[Date].[MonthNo] = 1)

I need the reference month, in this case january (Dim_Calendar[Date].[MonthNo] = 1), to be dynamic. Therefore, I cannot create this as a calculated column. 

Furthermore, I calculate the reference value in remaining months by 

Hours reference =
SUM(Hours) * [Hours ratio]

Per product, this solution works well and gives me the correct output

However, when having multiple products at the same time, Hours ratio calculates as a total for the product when I want it to keep the ratios as screenshots above. In the current solution, if including both product 1 and 2, the total Hours reference sum for 2021 is 2 675,26. 

My desired output is 758,00 (Product 1 above) + 1893,33 (Product 2 above) = 2 678,33

I would truly appreciate any tips, idead or solutions to this problem! Also attaching link to pbix with dummydata:

https://www.dropbox.com/s/3m7ane0lv6czxi3/DummyData.pbix?dl=0

Thank you!

[lbendlin · Accepted solution]

You are attempting to sum up products but are instead multiplying sums.  To fix that, use aggregator functions that calculate the values separately by product and month and then add them up.

Generally, when creating complex measures the mantra is "think like the grand total". In the  majority of cases the resulting code will then also work with individual cells (although it might look like overkill).

"use the hours spent per product produced in a given month as a reference for the remaining months in that year"

- this is not clear in your sample data. You seem to have pegged the January value rather than the March value. Please clarify.

[Anonymous · Accepted solution]

Hi @aspmag 

SUM dax does not know the existence of rows (it cannot evaluate row by row)-all it can do is add everything to the single column it presents after applying the filter.

SUMX dax can operate on multiple columns in the table, and can perform row-by-row evaluation in these columns.

In your case, it is to separate the product line by line, and then add the sum, so it is not suitable to use sum .

I create two measures , you can refer to them

Ratio = CALCULATE(SELECTEDVALUE(DummyData[Hours])/SELECTEDVALUE(DummyData[Quantity]),FILTER(ALLEXCEPT(DummyData,DummyData[ProductNo]),DummyData[Month]=1))

Reference = SUMX(DummyData,[Ratio]*DummyData[Quantity])

The effect is as shown :

I have attached my pbix file , you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[lbendlin]

Learn about the aggregator functions like SUMX function (DAX) - DAX | Microsoft Docs

Once you get the general idea of how they work you can then create your measure quite easily.  Remember to think like the grand total.

[aspmag]

Thank you for the reply Ibendlin.

Yes, aggregation of separately calculated values is what I'm looking for, but I'm not able to write the necessary DAX for it. Could you please give me an example of how you would solve it?

I'll try to clarify the reference month; I want to compare the actual production hours in a month with the hours it would have taken if the production was as effecient as the camparison month. In my example, I pegged January as an example. For product 1, meaning 250 (hours) / 500 (units) = 0,5 hours per product produced. For February, the actuals units produced is 520, meaning that if  the production was as effecient as January, it should've taken 0,5 * 520 = 260 hours. Actual hours used is 240, so the production is more effecient.

I pegged January in this case, but in reality I have a seperate measure with a logic that gives me the wanted comparison month. Do not worry about this however as I've solved that part. Dit that clarify your uncertainty?

My problem is, as you stated, that I need to calculate the values separately by product and then sum up and unfortunately I'm not able to do so.  

[lbendlin · Accepted solution]

You are attempting to sum up products but are instead multiplying sums.  To fix that, use aggregator functions that calculate the values separately by product and month and then add them up.

Generally, when creating complex measures the mantra is "think like the grand total". In the  majority of cases the resulting code will then also work with individual cells (although it might look like overkill).

"use the hours spent per product produced in a given month as a reference for the remaining months in that year"

- this is not clear in your sample data. You seem to have pegged the January value rather than the March value. Please clarify.