Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |