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

Need help of calculating percentage of population from two tables, many-to-one relationship

Hello,

Need help on calculating the percentage then display the percentage for a selected state in a card.

 

I have two tables.

Table 1 contains weekly subject counts for each state. 

State WeekCounts
AL12
AL21
AL35
AL43
GA11
GA25
GA36
GA41

 

 Tabel 2 contains the population of each state. 

StatePop
AL120
GA150

A many-to-one single direction (table2->table1) relationship was set up. Now I want to calculated the percentage of counts/population for each state and put the percertage in the card. For example. AL will be  (2+1+5+3)/120=0.09

 

Thank you!

 

Sam

 

1 ACCEPTED SOLUTION
FrankAT
Community Champion
Community Champion

Hi @Sam10 

take a look at the following solution:

02-11-_2020_22-42-25.png

 

 

Percentage Counts/Population = 
VAR _StateValue = MIN(Population[Pop])
VAR _SumOfCounts = SUM('Table'[Counts])
RETURN
    DIVIDE(_SumOfCounts,_StateValue)

 

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

View solution in original post

2 REPLIES 2
FrankAT
Community Champion
Community Champion

Hi @Sam10 

take a look at the following solution:

02-11-_2020_22-42-25.png

 

 

Percentage Counts/Population = 
VAR _StateValue = MIN(Population[Pop])
VAR _SumOfCounts = SUM('Table'[Counts])
RETURN
    DIVIDE(_SumOfCounts,_StateValue)

 

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Sam10
Frequent Visitor

Works like a charm! Thank you. 

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.