Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
I need to compute a weighted average for the consumer price.
Currently, CP YTD is computed as Average(DB(consumer price). However, if I use this formula I receive an arithmetic average.
I need to weigh the consumer price for NES YTD (Sales).
the formula needs to be something like: (CP YTD*NES YTD)/sum(NES YTD). however, I need to do that for each level.
for example:
for liv6 [(NES Ytd 541... * CP YTD 541... )/ sum (NES YTD 541... + NES YTD 541... and so on) ]
the denominator needs to be dynamically adjusted depending on the level that is analysed.
How can I do that in DAX?
Thanks for the help
Solved! Go to Solution.
Try to use the sumx function.
WA =
VAR total_ =
CALCULATE(
[NES YTD],
ALL(xxxxx)
)
VAR _Table =
ADDCOLUMNS(
SUMMARIZE(
Sales,
xxx[Row1],xxx[Row2]
),
"nes", [NES YTD],
"cp", [CP YTD]
)
RETURN
SUMX(
_Table ,
[cp] * [nes]/total_
)
Thanks for your reply!
I have just some further questions?
I really appreciate your help!
It refers to the column name of row labels. If the problem still exists, Please provide some sample data, preferably a pbix after removing sensitive data.
Try to use the sumx function.
WA =
VAR total_ =
CALCULATE(
[NES YTD],
ALL(xxxxx)
)
VAR _Table =
ADDCOLUMNS(
SUMMARIZE(
Sales,
xxx[Row1],xxx[Row2]
),
"nes", [NES YTD],
"cp", [CP YTD]
)
RETURN
SUMX(
_Table ,
[cp] * [nes]/total_
)
User | Count |
---|---|
123 | |
70 | |
67 | |
58 | |
53 |
User | Count |
---|---|
186 | |
93 | |
67 | |
63 | |
54 |