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
shanj
Frequent Visitor

Filter aggregated Card by Multiple Slicers

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.

5 REPLIES 5
shanj
Frequent Visitor

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

AllisonKennedy
Super User
Super User

@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


Please @mention me in your reply if you want a response.

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

nandukrishnavs
Super User
Super User

@shanj 

 

Can you provide sample tables and expected out, so that we will understand the scenario and can validate the answer before posting.


Regards,
Nandu Krishna

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.