Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
admiralman
Advocate II
Advocate II

Dynamic Weighted Average

I am trying to get the Average Price per month similar to the following. The average needs to change based upon the selection of the Location and Model slicers. Ultimately I need to be able to graph by month the Total of the WeightedCost...for instance 922.22 for Jan. Any help would be appreciated...that example file is located here.

 

 

2018-08-06_14-58-28.gif

7 REPLIES 7
v-juanli-msft
Community Support
Community Support

Hi @admiralman

I create new measures here to get what you want.

TotalCount1 =
CALCULATE (
    SUM ( Rentals[Count] ),
    FILTER (
        ALL ( Rentals ),
        [Location] = SELECTEDVALUE ( Rentals[Location] )
            && [Month] = SELECTEDVALUE ( Rentals[Month] )
    )
)


Weighting1 = MAX([Count])/[TotalCount1]

WeightedCost1 = SUM(Rentals[Rent])*[Weighting1]

WeightedCost2 = SUMX(Rentals,[WeightedCost1])

5.png

 

 

There are two tables above, they are different from the aggregation of each columns, please look at the pbix to find the difference.

 

Best Regards

Maggie

@v-juanli-msftI have updated this post becuase what I thought didn't work in the solution seems to work fine. However, the key is that I am going to want to be able to put this in a chart and graph by month with a totla price per month...not by model. When I select multiple months this does not seem to work.

Hi @admiralman

put this in a chart and graph by month with a totla price per month...not by model

Does this chart meet your goals?

1.png

 

When I select multiple months this does not seem to work.

What do you want to show when you select multiple months?

 

Best Regards

Maggie

 

I have also found that if you add a third model for NY in Jan that count will be included in the total count even if not selected in the slicer.

Hi @admiralman

If you want the Totalcount calculates based on selections of "Location","Month","Model", then you can use this formula, but it will calculate Totalcount for each row.

2.png

 

I would suggest you to give some expected results, then I can work directly for the purpose.

 

Best Regards

Maggie

 

 

I added a third model to show things a little more clear. As you can see, if I select all Models then the weighting, weightedcost2, and the total are what I would expect to see. However, if you only choose two models as in the second image things go askew. The total count continues to include the Prius even though filtered out by the slicer. Thus throwing off the weighting and the weightedcost2. If you graph this you will see the same result. I would expect the totalcount1 in image two to be 21 instead of 33 when only the Challenger and Malibu are selected.

 

 

1.gif2.gif

 

 

 

 

 

https://1drv.ms/u/s!Ao8vxCtY7cP9ijKeOZlbSTBffxDy

Anyone know if this is possible?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.