Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Ok, its probably easier to show you what im trying to create rather than try explain it
Date | Promotion Value | Order Value | All Orders |
01/01/2019 | £ 952.00 | £ 975.00 | £ 2,431.00 |
02/01/2019 | £ 708.00 | £ 731.00 | £ 2,829.00 |
03/01/2019 | £ 201.00 | £ 224.00 | £ 3,926.00 |
04/01/2019 | £ 799.00 | £ 822.00 | £ 2,335.00 |
05/01/2019 | £ 267.00 | £ 290.00 | £ 3,394.00 |
06/01/2019 | £ 196.00 | £ 219.00 | £ 2,903.00 |
07/01/2019 | £ 550.00 | £ 573.00 | £ 3,830.00 |
08/01/2019 | £ 544.00 | £ 567.00 | £ 2,784.00 |
09/01/2019 | £ 418.00 | £ 441.00 | £ 2,744.00 |
10/01/2019 | £ 555.00 | £ 578.00 | £ 3,765.00 |
11/01/2019 | £ 696.00 | £ 719.00 | £ 3,553.00 |
12/01/2019 | £ 739.00 | £ 762.00 | £ 2,264.00 |
13/01/2019 | £ 987.00 | £ 1,010.00 | £ 3,825.00 |
14/01/2019 | £ 720.00 | £ 743.00 | £ 3,336.00 |
15/01/2019 | £ 316.00 | £ 339.00 | £ 3,866.00 |
So for the first we had £952 of discounted order lines. The value of the whole order sum'd would be £975
The model looks is standard order structure (Header, Lines) and lines is connected to a "PromoLines" table that holds order numbers (and the line) that is on promotion.
What I want to do is, If the order appears in the PromoLines table, sum ALL the corresponding lines in the Lines table
Cheers
Solved! Go to Solution.
Hi @MCornish ,
Sorry for mu late respond, we can create a measure as below.
Measure =
VAR linkcolumn =
VALUES ( 'Table (2)'[LinkColumn] )
VAR ordernum =
VALUES ( 'Table (2)'[OrderNum] )
RETURN
IF (
ISFILTERED ( 'Table (2)'[PromoID] ),
CALCULATE (
SUM ( 'Table'[LineCost] ),
FILTER (
'Table',
'Table'[LinkColumn] IN linkcolumn
&& 'Table'[OrderNum] IN ordernum
)
),
BLANK ()
)
Pbix as attached as well.
With a small tweak it does what I need.
Measure =
VAR linkcolumn =
VALUES ( 'Table (2)'[LinkColumn] )
VAR ordernum =
VALUES ( 'Table (2)'[OrderNum] )
RETURN
IF (
ISFILTERED ( 'Table (2)'[PromoID] ),
CALCULATE (
SUM ( 'Table'[LineCost] ),
FILTER (
'Table',
'Table'[OrderNum] IN ordernum
)
),
BLANK ()
)
Basically I removed the
'Table'[LinkColumn] IN linkcolumn
from the SUM.
Cheers
Hi @MCornish ,
Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
The datasource is a PowerBI dataset containing confidential sales info. The est i can do is show you a mock up of the tables involved.
Lines
LinkColumn | OrderNum | LineNo | Product | LineCost | Qty |
123456-1 | 123456 | 1 | PROD01 | £ 1.99 | 1 |
123456-2 | 123456 | 2 | PROD02 | £ 2.99 | 1 |
123456-3 | 123456 | 3 | PROD03 | £ 4.50 | 1 |
123456-4 | 123456 | 4 | PROD04 | £ 3.99 | 1 |
PromoLines
LinkColumn | OrderNum | LineNo | PromoID |
123456-1 | 123456 | 1 | 245 |
123456-2 | 123456 | 2 | 654 |
So if I was looking at PromoID 245 the total for the promo is £1.99. But the Order value is £13.47 i.e. all the lines for that order.
PromoID 654 would return £2.99 and Order value is still £13.47
Hope this helps
Hi @MCornish ,
Sorry for mu late respond, we can create a measure as below.
Measure =
VAR linkcolumn =
VALUES ( 'Table (2)'[LinkColumn] )
VAR ordernum =
VALUES ( 'Table (2)'[OrderNum] )
RETURN
IF (
ISFILTERED ( 'Table (2)'[PromoID] ),
CALCULATE (
SUM ( 'Table'[LineCost] ),
FILTER (
'Table',
'Table'[LinkColumn] IN linkcolumn
&& 'Table'[OrderNum] IN ordernum
)
),
BLANK ()
)
Pbix as attached as well.
With a small tweak it does what I need.
Measure =
VAR linkcolumn =
VALUES ( 'Table (2)'[LinkColumn] )
VAR ordernum =
VALUES ( 'Table (2)'[OrderNum] )
RETURN
IF (
ISFILTERED ( 'Table (2)'[PromoID] ),
CALCULATE (
SUM ( 'Table'[LineCost] ),
FILTER (
'Table',
'Table'[OrderNum] IN ordernum
)
),
BLANK ()
)
Basically I removed the
'Table'[LinkColumn] IN linkcolumn
from the SUM.
Cheers
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |