Handling duplicates when performing a calculation on numbers from two unrelated tables

[Anonymous]

Hi All!

 (Edited to add a OneDrive link to my pbix file. Apples and Crabapples pbix file )

First, I want to thank AIB for helping me with my original question on this topic, linked here: Performing a calculation on numbers from two unrelated tables . AIB’s solution worked great for the tables that I offered in my question.

I applied the solution to my actual data and wrestled with a “table of multiple values was supplied where a single value was expected” error for far too long. It turns out that I have lines with duplicate shop and type entries in one of my data tables. Of course, I cannot change how the data is given to me nor simply delete the duplicate lines.

I tried the solution using a column with a unique entry for each line using the shop and type entries plus a number indicating if that entry was a duplicate line and a column that summed all crabapple amounts for a specific shop. The “table of multiple values was supplied where a single value was expected” error went away but I can’t get the variable Crabapples_ to return anything but a blank.

Can anybody show me where I’m going wrong with this? I’ll try to attach a copy of my PowerBI file so you can see exactly what I’ve done once I get a useable One Drive account. 

Thanks so much!

Apples and Crabapples pbix file

I can't figure out how to attach a copy of my PowerBI file so I'll copy the tables and the calculated columns I came up with.

New All Stores Data Set

Store	Item	Units	New Units I'm looking for	Notes:	Store + Item	Trying to create New Units I'm Looking for column	What VAR Crabapples_ returns
Uptown	Apples	7	7	No crabapples are from the Uptown store in the District data set	Uptown Apples	7
Uptown	Strawberries	9	9		Uptown Strawberries	9
Uptown	Grapes	4	4		Uptown Grapes	4
Downtown	Apples	5	1	4 crabapples are from the Downtown store in the District data set	Downtown Apples	5
Downtown	Bananas	3	3		Downtown Bananas	3
Downtown	Pears	10	10		Downtown Pears	10
Suburbs	Apples	7	4	3 crabapple is from the Suburb store in the District data set	Suburbs Apples	7
Suburbs	Cantalopes	6	6		Suburbs Cantalopes	6
Suburbs	Pineapple	2	2		Suburbs Pineapple	2
Rural	Apples	4	4	9 crabapples are from the Rural store in the District data set. Because this are more than the 4 apples in the All Stores data set the New Units number stays at 4.	Rural Apples	4
Rural	Cantalopes	1	1		Rural Cantalopes	1
Rural	Pineapple	2	2		Rural Pineapple	2

New District Data Set

Shop	Type	Amount	Shop + Type	Index	Incidence number for Shop + type	Shop + Type + Incidence	Adding multiple Crabapple entries
Uptown	Bananas	7	Uptown Bananas	0	1	Uptown Bananas	7
Uptown	Strawberries	9	Uptown Strawberries	1	1	Uptown Strawberries	9
Uptown	Grapes	4	Uptown Grapes	2	1	Uptown Grapes	4
Downtown	Bananas	5	Downtown Bananas	3	1	Downtown Bananas	5
Downtown	Crabapples	3	Downtown Crabapples	4	1	Downtown Crabapples	4
Downtown	Crabapples	1	Downtown Crabapples	5	2	Downtown Crabapples 2	4
Downtown	Pears	10	Downtown Pears	6	1	Downtown Pears	10
Suburbs	Crabapples	1	Suburbs Crabapples	7	1	Suburbs Crabapples	3
Suburbs	Cantalopes	6	Suburbs Cantalopes	8	1	Suburbs Cantalopes	6
Suburbs	Pineapple	2	Suburbs Pineapple	9	1	Suburbs Pineapple	2
Suburbs	Crabapples	2	Suburbs Crabapples	10	2	Suburbs Crabapples 2	3
Rural	Crabapples	9	Rural Crabapples	11	1	Rural Crabapples	9
Rural	Cantalopes	1	Rural Cantalopes	12	1	Rural Cantalopes	1
Rural	Pineapple	2	Rural Pineapple	13	1	Rural Pineapple	2

Where the formula for the "Trying to create new Units I'm looking for column" calculated column is:

Trying to create New Units I'm looking for column =

IF (

'All Stores'[Item] <> "Apples",

'All Stores'[Units],

VAR Crabapples_ =

LOOKUPVALUE (

District[Adding multiple Crabapple entries],

District[Shop + Type + Incidence],'All Stores'[Store + Item],

District[Type], "Crabapples"

)

RETURN

IF ( Crabapples_ > 'All Stores'[Units], 'All Stores'[Units], 'All Stores'[Units] - Crabapples_)

)

[amitchandak]

I am not able to get you calculations correctly. In display data also overlapped.

This what we do to get data from another table. We use lookup of function like MINX, MAXX, SUMX , All X function

Eaxmple I want to get data in a new column of Table 1 from Table 2, I will try

new column= minx(filter(table2,table1[col1] =table2[col1] && table1[col2] =table2[col2]),table1[col4])

This can be a new column or part of new column calculation .

Hope this can help. In not. If possible please share a sample pbix file after removing sensitive information.Thanks.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hi Amitchandak,

Can you tell me how to share a pbix file? I didn't see an option to add files to my original post.

Thanks!

[v-frfei-msft]

Hi @Anonymous ,

Please upload your files to One Drive and share the link here. Do mask your confidential information before that.