Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
Very new to PowerBI.
I have a measure of the rate of estimation for bills. This rate is grouped by three different dimensions f1,f2,f3. These dimensions have different number of categories. I want to be able to aggregate this measure and still filter by these categories.
I have dimension tables for for each f1,f2,f3 and have a computed field on each. This field is the rate of estimation (number estimations/ number of customers) for each.
I have created a crossjoin table of these three dimension tables. I also have a card with the average estimation rate measure from one of the tables eg. f1.
PowerBI only allows me to filter this average by f1.
I would like to be able to filter by all three dimensions f1,f2,f3.
For the table below, I am trying to filter the average of percent estimation by any combination of the filters. For example SSN system and Type1 type (120/400).
If I average percent estimation column as is when no filter is applied that average is not meanngful as it is the average by system, parish,type and month.
I would like to display the month average by default when no filter is applied.
Avg March: 54%, Avg April: 17%. Therefore the average to be displayed is 35% then filters are applied to this average.
system | parish | type | month | number estimates | total reads | percent estimation | ||||||
SSN | Town1 | Type1 | March | 100 | 200 | 50 | ||||||
YP | Town1 | Type2 | March | 200 | 350 | 57 | ||||||
AMI |
| Town1 | Type2 | April | 10 | 100 | 10 | |||||
SSN |
| Town2 | Type1 | April | 20 | 200 | 10 | |||||
QL | Town2 | Type2 | April | 60 | 300 | 20 | ||||||
SSN | Town2 | Type2 | April | 40 | 100 | 40 | ||||||
YP | Town3 | Type2 | March | 10 | 110 | 9 |
any help would be greatly appreciated.
Hi @shanj ,
Would you please show me more details about your expected output? How did you get "Avg March: 54%, Avg April: 17%. Therefore the average to be displayed is 35% then filters are applied to this average."?
Best Regards,
Dedmon Dai
@shanj Not sure why you need a crossjoin. I would recommend using a MEASURE not a field/column to calculate the average, this should then let you filter by all three dimenions tables (f1, f2, f3) provided that there is a fact table containing all the estimates that is related to each of the dimension tables. You are wanting to get a star schema relationship model to make this work.
Please post a photo of your model view with relationships if you still need help.
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
https://sites.google.com/site/allisonkennedycv
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi @shanj,
Would you please inform us more detailed information( your sample data(by OneDrive for Business)) if possible? Then we will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Dedmon Dai
Can you provide sample tables and expected out, so that we will understand the scenario and can validate the answer before posting.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |