How to use Summarizecolumn in this case

[pablo_paiva]

Hi,

I've been working with Power BI for the last weeks and I tried to find a solution for this simple issue.

Look at the table sample below, I have the first 5 columns are data consolidated from different sources (data services analysis server & sharepoint files) so data is quite large and takes a lot of time to load in my pc. I want to find the most efficient solutionto add the columns (in red) to reflect the qty of parts in shortage or excess at highest organization level (Level 1) by PN.

Originally, I had the table without the sublevel, so it was very easy to create 2 columns and 2 measures to display the result in a  dashboard, but when I added the sublevel I couldn't find the solution

Level 1	Sublevel	Part	Qty Required	Qty Available	Qty Required @ Level 1	Qty Available @ Level 1	Qty Shortage @ Level 1	Qty Excess @ Level 1	Qty Shortage % @ Level 1	Qty in Excess % @ Level 1
A	X	PN1	2	0	2+3+4 = 9	0+4+1 = 5	9-5=4	0	(9-5)/5=80%
A	Y	PN1	3	4	2+3+4 = 9	0+4+1 = 5	9-5=4	0	(9-5)/5=80%
A	Z	PN1	4	1	2+3+4 = 9	0+4+1 = 5	9-5=4	0	(9-5)/5=80%
B	U	PN1	1	3	1+2+3 = 6	3+3+3=9	0	9-6=3		(9-6)/6=50%
B	V	PN1	2	3	1+2+3 = 6	3+3+3=9	0	9-6=3		(9-6)/6=50%
B	W	PN1	3	3	1+2+3 = 6	3+3+3=9	0	9-6=3		(9-6)/6=50%

I thought using:

- Group by in Editor Query by Level 1 & Part and later try to join the tables. But as it takes too much time to create the tables, I think that I'm unnecessary increasing the size of my report.

- Use a DAX expression to add a column, but I get an incorrect result or it doesn't work:

1. Qty Required @ Level 1 = SUMX(summarize('Table',[Level 1], [Part]),'Qty Required')

(Incorrect result, huge number)

2. Qty Required @ Level 1 = SUMMARIZECOLUMNS('Table',[Level 1], [Part],"QTY REQ Lev1",'Table'[Qty Req])

(The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.)

Any recommendations? 

Writing the DAX expression for the columns is more efficient than creating a table in Editor Query?

Thanks in advance,

Pablo

[v-chuncz-msft · Accepted solution]

@pablo_paiva,

You may use DAX below.

Column =
SUMX (
    FILTER (
        'Table',
        'Table'[Level 1] = EARLIER ( 'Table'[Level 1] )
            && 'Table'[Part] = EARLIER ( 'Table'[Part] )
    ),
    'Table'[Qty Required]
)