cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EbyEaso
Helper I
Helper I

Find percentage of items from a list compare to the master list.

Dear Friends,

 

I need help with DAX measure.

 

I have two list here. master list and the list from data entry people.

 

I have a master list contains country and items related to the each country. This is stable and maybe updated later according to the requirement. Sample list as below.

 

CountryItemItem ID
USACHAIR1
AUSTRALIATABLE2
INDIAWHITE BOARD3
BRAZILCURTAIN4
CHINAINSTRUMENTS5
MALAYSIAUTENSILS6
USACHAIR7
AUSTRALIATABLE8
INDIAWHITE BOARD9
BRAZILCURTAIN10
CHINAINSTRUMENTS11
MALAYSIAUTENSILS12
USACHAIR13
AUSTRALIATABLE14
INDIAWHITE BOARD15
BRAZILCURTAIN16
CHINAINSTRUMENTS17
MALAYSIAUTENSILS18
USACHAIR19
AUSTRALIATABLE20
INDIAWHITE BOARD21
BRAZILCURTAIN22
CHINAINSTRUMENTS23
MALAYSIAUTENSILS24
USACHAIR25
AUSTRALIATABLE26
INDIAWHITE BOARD27
BRAZILCURTAIN28
CHINAINSTRUMENTS29
MALAYSIAUTENSILS30

 

So our data entry people enter the data as thy receive into the system which looks like below.

 

CountryItemItem ID
USACHAIR1
AUSTRALIATABLE2
INDIAWHITE BOARD3
BRAZILCURTAIN4
CHINAINSTRUMENTS5
MALAYSIAUTENSILS6
USACHAIR7
AUSTRALIATABLE8
INDIAWHITE BOARD9
BRAZILCURTAIN10
CHINAINSTRUMENTS11
MALAYSIAUTENSILS12
USACHAIR13
AUSTRALIATABLE14

 

So I need to find out the total percentage of data entered into the system by country vise compared to the master list.

 

I'm new to the Power BI with little knowledge. So I need some help.

 

Thank You in advance.

2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

Hi @EbyEaso 

Try this measure in a card visual:

Measure =
DIVIDE ( COUNTROWS ( INTERSECT ( Table1, Table2 ) ), COUNTROWS ( Table1 ) )

 Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

Anonymous
Not applicable

@EbyEaso You Can use below formula

 

Comparision = Calculate(DIVIDE ( COUNTROWS ( INTERSECT ( Table1, Table2 ) ), COUNTROWS ( Table1 ) ))

 

Calculate will help to easily transition the context .

 

@AlB  Thank you 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

@EbyEaso You Can use below formula

 

Comparision = Calculate(DIVIDE ( COUNTROWS ( INTERSECT ( Table1, Table2 ) ), COUNTROWS ( Table1 ) ))

 

Calculate will help to easily transition the context .

 

@AlB  Thank you 

View solution in original post

Wow!

 

This is awesome. Thank You @AlB , @Anonymous . Appreciate you both.

 

Alos I have a question just came up in mind regardin the same table. What if my master list is in different table by country wise. In the previous senario all countries are in in same master list. Now i have master list for each country. 

 

Without appending it is ther a measure to find the same calculation in thise senario?

 

Just my thaught if I had to face that.

@EbyEaso 

Not really. You could do a UNION( ) of all the tables within the measure and work on that as we did in the code shown earlier. I'm not sure that would be of much help. It's probably best to do the merge of all those tables in Power Query, unless you want to keep them separately

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

AlB
Super User
Super User

Hi @EbyEaso 

Try this measure in a card visual:

Measure =
DIVIDE ( COUNTROWS ( INTERSECT ( Table1, Table2 ) ), COUNTROWS ( Table1 ) )

 Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.