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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.