topic Find percentage of items from a list compare to the master list. in DAX Commands and Tips

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

EbyEaso — Tue, 20 Oct 2020 03:58:28 GMT

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.

Thank You in advance.

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

AlB — Tue, 20 Oct 2020 05:30:55 GMT

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

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

Anonymous — Tue, 20 Oct 2020 06:39:51 GMT

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

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

EbyEaso — Tue, 20 Oct 2020 11:12:27 GMT

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.

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

AlB — Tue, 20 Oct 2020 11:58:50 GMT

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