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

[nj17]

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?

[v-yetao1-msft]

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 ?

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]

@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.

[BA_Pete]

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

[nj17]

Hi @BA_Pete 

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

In Power BI

 I have added scenarios what I want:

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

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

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?

Thank you

[BA_Pete]

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]

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