Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
EbyEaso
Helper III
Helper III

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 

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.