cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User II
Super User II

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
Super User II
Super User II

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

Sam10
Frequent Visitor

Works like a charm! Thank you. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.