Forum Discussion

admiralman's avatar
admiralman
Advocate II
8 years ago

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.

 

 

Need Help

7 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    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])

     

     

    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

    • admiralman's avatar
      admiralman
      Advocate II
      8 years ago

      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.

      • admiralman's avatar
        admiralman
        Advocate II
        8 years ago

        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.