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 have a table with Columns- Module, Subcategory, Num_of_users, Cost per user, Total Cost etc. I am using slicers with option to multi-select subcategory column values. I am able to create a Tile card to display average cost per unique combination of [Module+Subcategory], but facing issue when trying to select multiple values in the slicer for Subcategory.
Example of the formula being used here to calculate Total Cost for slicer value selection:
Solved! Go to Solution.
In this case please try
please try
Total Cost =
SUMX (
VALUES ( 'Data Refined'[Sub-category] ),
CALCULATE (
AVERAGEX (
'Data Refined',
'Data Refined'[Average Quoted price ] * 'User Count Parameter'[User Count Parameter Value]
)
)
)
Hi Tamerj1,
Thankyou for your suggestion but this formula isnt working correctly, more explanation below-
This formula takes the sum of the average values per category first, then takes an overall average....what happens in this case is lets say there are 2 rows for Category A with values 800 and 1000, 1 row for Category B with value 1200, then this formula is taking SUM of [Average of price for Category A=900, 2 times] + [Average of price for Category B=1200, 1 time] which comes out to be 3000, then taking another Average due to the outside function, and dividing (3000/2)=1500, because it finds count of distinct categories=2.
I am looking to take Sum of averages for individual category dynamically, based on slicer selection, so the desired number should be 900+1200=2100 for avg of 2 categories
In this case please try
please try
Total Cost =
SUMX (
VALUES ( 'Data Refined'[Sub-category] ),
CALCULATE (
AVERAGEX (
'Data Refined',
'Data Refined'[Average Quoted price ] * 'User Count Parameter'[User Count Parameter Value]
)
)
)
Hi @chiragjagga
please try
Total Cost =
AVERAGEX (
VALUES ( 'Data Refined'[Sub-category] ),
CALCULATE (
SUMX (
'Data Refined',
'Data Refined'[Average Quoted price ] * 'User Count Parameter'[User Count Parameter Value]
)
)
)
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |