cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nj17
Helper II
Helper II

Do not add up duplicate values from 2 files in power bi

Hi All,

 

I have 2 csv files having sales data for different countries.I have same data available for india and srilanka in both the files.

now when i select india from slicer it should not show duplicate data and should show data from any one of the file and likewise for srilanka.

Also when i do not select any of these countries then it should not add up the duplicate values should show me values from any one of the file.

@Greg_Deckler  @amitchandak 

How can i acheive this in power bi?

6 REPLIES 6
Ailsa-msft
Community Support
Community Support

Hi @nj17 

From your original data , when you select India and select no Call Center ,the value for Sales is 1000 , which is correct .So why did you want it 500 ? Can you explain in detail ?

Ailsamsft_0-1632807045625.png

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

nj17
Helper II
Helper II

@BA_Pete , I forgot to mention that I cant remove these records as these 2 files are coming from 2 different call center so lets say i have 2 call center A and B, so when I select call center A and then choose india or srilanka it will give me correct number.Also If i select Call center B and then choose india or srilanka then also it would give me correct number but when I do not select any call center then the values get added and i dont want that.

Hi @nj17 ,

 

So, the India rows in the call centre A file hold completely different information from the India rows in the call centre B file? They are not duplicated information, you just want to stop them being added together when you filter your report on India?

 

If this is the case, then I would still suggest appending your two tables as this generally makes modelling and calculations simpler, then you can create distinct measures for each of your different values, something like this:

_callCentreA_srilanka =
CALCULATE(
  DISTINCTCOUNT(appendedTable[callNumber]),
  appendedTable[country] = "Sri Lanka",
  appendedTable[callCentre] = "Call Centre A"
)

_callCentreB_srilanka =
CALCULATE(
  DISTINCTCOUNT(appendedTable[callNumber]),
  appendedTable[country] = "Sri Lanka",
  appendedTable[callCentre] = "Call Centre B"
)

_callCentreA_india =
CALCULATE(
  DISTINCTCOUNT(appendedTable[callNumber]),
  appendedTable[country] = "India",
  appendedTable[callCentre] = "Call Centre A"
)

_callCentreB_india =
CALCULATE(
  DISTINCTCOUNT(appendedTable[callNumber]),
  appendedTable[country] = "India",
  appendedTable[callCentre] = "Call Centre B"
)

 

You can then display these values completely independently from one another.

 

Pete

Hi @BA_Pete 

I have appended the data.this is the sample source file

nj17_5-1632672781162.png

 

In Power BI

 

nj17_6-1632672847275.png

 

 I have added scenarios what I want:

 

1.When I select India and A then I get 500 which is correct

nj17_2-1632672192290.png

 

2.When I select India and B i get 500 which is also correct

nj17_3-1632672227912.png

3.but when I do not select any call center then I get 1000 which is a double counting for me I want it 500.how can I achieve this?

nj17_4-1632672256660.png

Thank you

Hi @nj17 ,

 

I'm struggling to follow your use-case here, but if you want to change measure behaviour based on a slicer selection, then you could use a measure like this:

_measure =
IF(
  HASONEVALUE(yourTable[Call Centre]),
  [totalSales],
  //Function you want to apply if no slicer selection, like average or Call Centre A sales only etc.
)

 

Pete

BA_Pete
Super User
Super User

Hi @nj17 ,

 

It sounds like both of your files have the same columns in them (same names and capitalisation) and, presumably, each have the same data types as one another.

In this case, I would APPEND these two files together in Power Query then select the whole appended table (Ctrl+A) and remove duplicates.

Use this new appended table as your data source in your report.

 

Pete

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!