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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.