Forum Discussion
Comparing filtered vs unfiltered total
Hi,
I am trying to compare the total value/sum of a field (‘sales’) vs the value/sum from a specific customer (‘sales’ done by ‘client X’) and I am not sure how to get there. The additional issue that I have the ‘client’ values in a separate data source.
An extremely simplified example is the following (there is a significant number of values in each data source):
Source A
Transaction ID, Client Name , Other fields…
TR01, Customer A, …
TR02, Customer B, …
TR03, Customer A, …
TR04, Customer C, …
Source B
Transaction ID, Value, Other fields…
TR01, 100£, …
TR02, 100£, …
TR03, 50£, …
TR04, 100£, …
I am trying to get a chart where I have a column of the total sales on one side (350£ in the example above) and a column sales only with a specific customer (e.g. Customer A) on the other side (150£ in the example above).
Customer A sales are basically included both in the "Customer Sales A" column AND in the "Total Sales" column.
Please let me know if this is not clear.
Many thanks in advance for any help.
Assuming you have a relationship on TransactionID between the tables (if it is 1:1 relationship, you could consider merging those tables in power query), you just need two measures:
Total = sum(SourceB[Value])
CustomerA Total = calculate([Total], SourceA[Client Name] = "Customer A")
Note: you should also consider making a Customers table in Power Query with the unique list of Customers and their metadata.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
3 Replies
- mahoneypat
Microsoft Employee
Assuming you have a relationship on TransactionID between the tables (if it is 1:1 relationship, you could consider merging those tables in power query), you just need two measures:
Total = sum(SourceB[Value])
CustomerA Total = calculate([Total], SourceA[Client Name] = "Customer A")
Note: you should also consider making a Customers table in Power Query with the unique list of Customers and their metadata.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Many thanks mahoneypat . This is exactly what I needed.
- Greg_Deckler
Community Champion
You need to use ALL or ALLEXCEPT or REMOVEFILTERS to get your unfiltered total.
