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
syddemo
Regular Visitor

Get average using Main Category column only but also include subcategories in the table.

I have data of this data of thre column and I want output as Avg by Column. How to achive it?

The main problem is all three columns are from different tables but I have joined them.

 

CountrystatepopulationAvg by Country
Indiapunjab20004750
Indiaharyana40004750
IndiaHimachal60004750
IndiaUP70004750
canadatoronto1000015000
canadaColumbia2000015000
1 ACCEPTED SOLUTION
dedelman_clng
Community Champion
Community Champion

Create a measure:

 

Avg by Country =
CALCULATE (
    AVERAGE ( 'Table'[population] ),
    ALLEXCEPT ( 'Table', 'Table'[Country] )
)

 

2020-08-07 09_27_30-Untitled - Power BI Desktop.png

 

Hope this helps

David

View solution in original post

4 REPLIES 4
dedelman_clng
Community Champion
Community Champion

Create a measure:

 

Avg by Country =
CALCULATE (
    AVERAGE ( 'Table'[population] ),
    ALLEXCEPT ( 'Table', 'Table'[Country] )
)

 

2020-08-07 09_27_30-Untitled - Power BI Desktop.png

 

Hope this helps

David

Hi @dedelman_clng. Thanks for your response. Will this work even if I am using all coumns from different tables (joins)?

As @az38 said, yes, if the model is correct.  Can you share a screen shot of your model showing the tables and their relationships?

az38
Community Champion
Community Champion

@syddemo 

it should work if you have correct relationships.

or what do you mean by JOIN exactly? SQL-JOIN or DAX-JOIN or what?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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.