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.
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.
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
@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?
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.
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.
Anyone know if this is possible?
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |