DAX weighted average based on another dimension

Question

I have the following data:

DATE        COUNTRY     ITEM        Value
2005-01-01  UK          op_rate     30%
2005-01-01  UK          proc        1000
2005-01-01  UK          export      750 
2005-01-01  ITA         op_rate     45%
2005-01-01  ITA         proc        500 
2005-01-01  ITA         export      350

Basically, data in normal format, which includes both ratios (the op_rate) and other items such as exported volumes and processed volumes ("proc").

I need to aggregate by SUM for "proc" and "export", but not for the "op_rate", for which I need a weighted average by "proc".

In this case the aggregated op_rate would be: 0.45*500 + 0.30*1000 = 0.35 // instead of a .75 SUM or 0.375 AVERAGE

All example I find for weighted average are across measures, but none covers using other dimensions.

Any help most welcome!

This data structure is mixing apples and oranges. I would pivot this table for "Item" (i.e, convert it into a table: date, country, op_rate, proc, export). Then your DAX will be very streightforward. — RADO, Jul 13 '18 at 14:57
You will have to restructure your data in some way in order to do the calculation you want (since you need to treat `proc` and `op_rate` as if they are in the same row). You can either restructure it in an unnecessarily complex measure or else you can restructure it in the query editor while adhering to the good practice of not storing different data types in the same column. I can help with the latter, but not interested in helping with the former. — Alexis Olson, Jul 13 '18 at 17:04
Again, the model is larger than this, and the exception to the op-rate is worth having to avoid restructuring a number of other calculations. You can not assume to know better as you don’t have the full context, which must be simplified here for practicality. Pivoting as you say would force me to add an unnecessary number of other measures. If you have an answer to the question asked be my guest. Otherwise you can refrain from commenting. Many thanks. — vale_p, Jul 13 '18 at 17:14

score 1 · Accepted Answer · answered Jul 21 '18 at 12:00

I understand that you are reluctant to change your model. The problem you have here is that you are trying to consume a highly normalised table and use it for analysis using an OLAP tool. OLAP tools prefer Fact/Dim star schemas and Tabular/PowerBI is no different. I suspect that this is going to continue to problems with future requirements too. Taking the hit on changing the structure now is the best time to do it as it will get more difficult the longer you leave it.

This isn't to say that you can't do what you want using the tools, but the resulting dax will be less efficient and the storage required will be sub-optimal.

So with that caveat/lecture given (!) here is how you can do it.

op_rate_agg =
VAR pivoted =
    ADDCOLUMNS (
        SUMMARIZE ( 'Query1', Query1[COUNTRY], Query1[DATE] ),
        "op_rate", CALCULATE ( AVERAGE ( Query1[Value] ), Query1[ITEM] = "op_rate" ),
        "proc", CALCULATE ( SUM ( Query1[Value] ), Query1[ITEM] = "proc" )
    )
RETURN
    DIVIDE ( SUMX ( pivoted, [op_rate] * [proc] ), SUMX ( pivoted, [proc] ) )

It is really inefficient as it is having to build your pivoted set every execution and you will see that the query plan is having to do a lot more work than it would if you persisted this as a proper Fact table. If your model is large you will likely have performance issues with this measure and any that references it.

Many thanks, this is what I was looking for. There's good reason for which the data needs to be normal -- namely, the exception in this case is worth rewriting all the other calculations. Energy statistics in general often need calculation across different dimension depending on the context, but that's another story. Many thanks and have a good day. — vale_p, Jul 22 '18 at 18:07

score 0 · Answer 2 · answered Jul 13 '18 at 15:33

0

@RADO is correct. You should definitely pivot your ITEM column to get this.

Then a weighted average on op_rate can be written simply as

= DIVIDE(
      SUMX(Table3, Table3[op_rate] * Table3[proc]),
      SUMX(Table3, Table3[proc]))

answered Jul 13 '18 at 15:33

Alexis Olson

38,724
7
42
64

Same as above - the table structure is fixed. There are many other non-percentage items with hierarchies - pivoting them all is not an option. Also, model needs to be dynamic, not an aggregated fixed view. So definitely needs that structure. – vale_p Jul 13 '18 at 16:46

DAX weighted average based on another dimension

2 Answers2