Forum Discussion
New Measure based on conditions in two data sets
Hi All,
New to PowerBi and could really do with some help after spending almost a day on trying to figure this out!
I have two data sets:
- A table of sales
Customer ID | Product | Qty | Unit Value | Sales Value |
123 | Product 1 | 2 | £1 | £2 |
123 | Product 2 | 5 | £1 | £5 |
456 | Product 1 | 3 | £2 | £6 |
456 | Product 3 | 2 | £10 | £20 |
789 | Product 3 | 1 | £10 | £10 |
- A table of credits
Customer ID | Product | Credit Qty | Unit Value | Credit Value |
123 | Product 1 | 1 | £1 | £1 |
456 | Product 1 | 3 | £2 | £6 |
I want to be able to create a table visual that shows credits against Customer ID/Product:-
Customer ID | Product | Qty | Unit Value | Sales Value | Credit Value | Value |
123 | Product 1 | 2 | £1 | £2 | £1 | £1 |
123 | Product 2 | 5 | £1 | £5 |
| £5 |
456 | Product 1 | 3 | £2 | £6 | £6 | £0 |
456 | Product 3 | 2 | £10 | £20 |
| £20 |
789 | Product 3 | 1 | £10 | £10 |
| £10 |
Can this be done by creating a new measure?
Any help would be really appreciated! 🙂
Hi, hjcroo ,
You could create two measure.
ValueCredit = CALCULATE(SUM('Table (2)'[Credit Value]),FILTER('Table (2)',[CustomerID]=MAX('Table'[CustomerID])&&[Product]=MAX('Table'[Product _Qty])))value = SUM('Table'[Sales Value])-[ValueCredit]The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- James-Harpin
Resolver IV
Hi hjcroo
I have created a solution for you, firstly I linked the tables based on ID as the main relationship, and Product as an inactive relationship
I then created 2 measures, one for Value Credit:
And one for Value Value:
I then put all the Sales tables columns into a table and the 2 measures and got the following outcome:
Let me know if this solution works for you 😊
Hi James,
Firstly - Thank you so much for looking at this for me!
I think its going to work ...... The issue I have is the relationships between the tables....
Both Product to Product /ID to ID: I cant do 1to1/* to 1 - only allowing me to do *to* and then says
- James-Harpin
hjcroo That's fine shouldn't be an issue
v-yalanwu-msftCommunity Support
Hi, hjcroo ,
You could create two measure.
ValueCredit = CALCULATE(SUM('Table (2)'[Credit Value]),FILTER('Table (2)',[CustomerID]=MAX('Table'[CustomerID])&&[Product]=MAX('Table'[Product _Qty])))value = SUM('Table'[Sales Value])-[ValueCredit]The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
