Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi.
I have a table like this:
Orderno | Articlecode | Amount |
10001 | 246211 | 100 |
10001 | MDAHELG | 400 |
10002 | 299093 | 400 |
10002 | MDAHELG | 400 |
10002 | 365256 | 600 |
10003 | 299095 | 300 |
10004 | 366494 | 500 |
10004 | MDAHELG | 400 |
I want to summarize the amounts on only a few of this rows.
I want to summarize the rows that have this Articlecodes (hardcode is ok):
* 246211, 299093 and 299095
BUT i also want to add the sum for the rows that have the code MDAHELG and have the same orderno as the rows with the same Articlecodes as above) 246211, 299093 and 299095)
Like this:
Orderno | Articlecode | Amount |
10001 | 246211 | 100 |
10001 | MDAHELG | 400 |
10002 | 299093 | 400 |
10002 | MDAHELG | 400 |
10002 | 365256 | 600 |
10003 | 299095 | 300 |
10004 | 366494 | 500 |
10004 | MDAHELG | 400 |
The Articlecode 365256 and 366494 are not going to be summarized and either MDAHELG that are related to Orderno 10004 with Articlecode 366494.
The sum should be 1600.
Solved! Go to Solution.
Hi @PBIuser73 ,
I think you can try this code to create a measure.
Measure =
VAR _LIST = { "246211", "299093", "299095" }
VAR _ArticlecodeList = { "246211", "299093", "299095", "MDAHELG" }
VAR _OrdernoList =
CALCULATETABLE (
VALUES ( 'Table'[Orderno] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Articlecode] IN _LIST )
)
RETURN
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER (
'Table',
'Table'[Orderno]
IN _OrdernoList
&& 'Table'[Articlecode] IN _ArticlecodeList
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @PBIuser73,
Can you please try the following:
TotalAmount =
CALCULATE(
SUM('Table'[Amount]),
'Table'[Articlecode] IN {"246211", "299093", "299095", "MDAHELG"},
'Table'[Orderno] IN
CALCULATETABLE(
VALUES('Table'[Orderno]),
'Table'[Articlecode] IN {"246211", "299093", "299095"}
)
)
Hope this helps.
Hi.
It did not work. The amount got a lot bigger than expected.
Frank
Hi @PBIuser73 ,
I think you can try this code to create a measure.
Measure =
VAR _LIST = { "246211", "299093", "299095" }
VAR _ArticlecodeList = { "246211", "299093", "299095", "MDAHELG" }
VAR _OrdernoList =
CALCULATETABLE (
VALUES ( 'Table'[Orderno] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Articlecode] IN _LIST )
)
RETURN
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER (
'Table',
'Table'[Orderno]
IN _OrdernoList
&& 'Table'[Articlecode] IN _ArticlecodeList
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |