Want to show weighted average value DAX

[AshDil]

Hi,

I have data as follows:

Category    HourRate    Value
C                4.44             94.09
D               8.13              100

I want to calculate following:

Weighted average factor for Category C : 4.44 / 12.57 (i.e,Sum of Hourrate for Cat C & D) = 0.353

Weighted average factor for Category D : 8.13 / 12.57 (i.e,Sum of Hourrate for Cat C & D) = 0.647

And I want to show the following in KPI

In Slicer Cat C is selected,

Measure value = 94.09 

In Slicer Cat C & D are selected,

Measure Value = 94.09 * 0.353 + 100 * 0.647 = 97.89

Please help me to do.

Thanks,

AshDil.

[Anonymous · Accepted solution]

Hi @AshDil ，

You can create a measure as below to get it:

Measure = 
VAR _sumofrate =
    CALCULATE ( SUM ( 'Table'[HourRate] ), ALLSELECTED ( 'Table' ) )
VAR _tab =
    ADDCOLUMNS (
        'Table',
        "nvalue", 'Table'[Value] * DIVIDE ( 'Table'[HourRate], _sumofrate )
    )
RETURN
    SUMX ( _tab, [nvalue] )

Best Regards

[AshDil]

Hi @Anonymous ,

Thanks. It worked.

Regards,

AshDil

[Vera_33]

Hi @AshDil 

Here is one way, assume the slicer column from your fact table

test = 
VAR T1=ADDCOLUMNS('yourTable',"AVG",'yourTable'[HourRate]/SUM('Table'[HourRate])*'yourTable'[Value])
RETURN
SUMX(T1,[AVG])