cancel
Showing results for
Did you mean:
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.

 Country Item Item ID USA CHAIR 1 AUSTRALIA TABLE 2 INDIA WHITE BOARD 3 BRAZIL CURTAIN 4 CHINA INSTRUMENTS 5 MALAYSIA UTENSILS 6 USA CHAIR 7 AUSTRALIA TABLE 8 INDIA WHITE BOARD 9 BRAZIL CURTAIN 10 CHINA INSTRUMENTS 11 MALAYSIA UTENSILS 12 USA CHAIR 13 AUSTRALIA TABLE 14 INDIA WHITE BOARD 15 BRAZIL CURTAIN 16 CHINA INSTRUMENTS 17 MALAYSIA UTENSILS 18 USA CHAIR 19 AUSTRALIA TABLE 20 INDIA WHITE BOARD 21 BRAZIL CURTAIN 22 CHINA INSTRUMENTS 23 MALAYSIA UTENSILS 24 USA CHAIR 25 AUSTRALIA TABLE 26 INDIA WHITE BOARD 27 BRAZIL CURTAIN 28 CHINA INSTRUMENTS 29 MALAYSIA UTENSILS 30

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

 Country Item Item ID USA CHAIR 1 AUSTRALIA TABLE 2 INDIA WHITE BOARD 3 BRAZIL CURTAIN 4 CHINA INSTRUMENTS 5 MALAYSIA UTENSILS 6 USA CHAIR 7 AUSTRALIA TABLE 8 INDIA WHITE BOARD 9 BRAZIL CURTAIN 10 CHINA INSTRUMENTS 11 MALAYSIA UTENSILS 12 USA CHAIR 13 AUSTRALIA TABLE 14

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.

2 ACCEPTED SOLUTIONS
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

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

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

Helper I

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.

Super User

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

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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!