Subtracting values in same column until the value becomes 0

[dodiyal]

Hello Team,

I have 2 datasets where the-

-first dataset is for customer grop,week number and no.of lines being delivered to customer.

-Second dataset is only customer group

Now i wish to do a burn down chart in data set 1 which will vary if we select any customer from dataset 2 is slected (without connecting the files).

Please see below as an example what i need your help on-

Suppose i selcted Qatar in dataset 2 then burn down in chart 1 should Subtracting number of lines in same column until the value becomes 0 only for Qatar.

In this example there are 167 total lines for Qatar

So WK5  will show 167-25=142

And it goes on till the value becomes 0

If no selcection in data set 2 is made then sum should be all.

So WK 5 will show

WK5- 308-95=213

and goes on till the value becomes 0

I could create the dax but it is not correct, i find the data or your review.

Please help

data 1

Customer Group	week number	No. of Lines	week number graph
9 AIR CO., LTD.	202305	3	5
AAR ALLEN AIRCRAFT	202305	19	5
AAR ENGINEERING SERVICES-ASIA	202305	1	5
AEGEAN AIRLINES	202305	2	5
CEBU Air, Inc	202305	33	5
QANTASLINK	202305	12	5
QATAR AIRWAYS	202305	25	5
AAR ALLEN AIRCRAFT	202306	1	6
CEBU Air, Inc	202306	2	6
QATAR AIRWAYS	202306	9	6
AEGEAN AIRLINES	202307	2	7
CEBU Air, Inc	202307	3	7
QANTASLINK	202307	1	7
QATAR AIRWAYS	202307	13	7
AAR ALLEN AIRCRAFT	202308	3	8
AEGEAN AIRLINES	202308	2	8
CEBU Air, Inc	202308	2	8
QATAR AIRWAYS	202308	21	8
AEGEAN AIRLINES	202309	2	9
CEBU Air, Inc	202309	1	9
QANTASLINK	202309	1	9
QATAR AIRWAYS	202309	12	9
AAR ALLEN AIRCRAFT	202310	2	10
AEGEAN AIRLINES	202310	1	10
CEBU Air, Inc	202310	14	10
QANTASLINK	202310	1	10
QATAR AIRWAYS	202310	8	10
AEGEAN AIRLINES	202311	7	11
CEBU Air, Inc	202311	1	11
QANTASLINK	202311	3	11
QATAR AIRWAYS	202311	7	11
AEGEAN AIRLINES	202312	2	12
AER LINGUS	202312	1	12
CEBU Air, Inc	202312	1	12
QATAR AIRWAYS	202312	2	12
AAR ALLEN AIRCRAFT	202313	1	13
AEGEAN AIRLINES	202313	2	13
AER LINGUS	202313	1	13
CEBU Air, Inc	202313	2	13
QANTASLINK	202313	1	13
QATAR AIRWAYS	202313	10	13
QANTASLINK	202314	1	14
QATAR AIRWAYS	202314	8	14
CEBU Air, Inc	202315	1	15
QATAR AIRWAYS	202315	6	15
AEGEAN AIRLINES	202316	5	16
CEBU Air, Inc	202316	1	16
QANTASLINK	202316	1	16
QATAR AIRWAYS	202316	2	16
CEBU Air, Inc	202317	1	17
QATAR AIRWAYS	202317	8	17
QATAR AIRWAYS	202318	11	18
AEGEAN AIRLINES	202319	1	19
QATAR AIRWAYS	202319	11	19
QATAR AIRWAYS	202320	1	20
QATAR AIRWAYS	202321	2	21
QATAR AIRWAYS	202322	1	22
QATAR AIRWAYS	202323	3	23
QATAR AIRWAYS	202324	1	24
QATAR AIRWAYS	202325	1	25
QATAR AIRWAYS	202326	2	26
QATAR AIRWAYS	202327	1	27
QATAR AIRWAYS	202330	1	30
QATAR AIRWAYS	202334	1	34

data 2

Customer Group

9 AIR CO., LTD.

AAR ALLEN AIRCRAFT

AAR ENGINEERING SERVICES-ASIA

AEGEAN AIRLINES

AER LINGUS

CEBU Air, Inc

QANTASLINK

QATAR AIRWAYS

Etihad

Jetairways

Delta

American

[Ashish_Mathur]

Hi,

Would it be possible to show the expected result in another column of Table1?

[Greg_Deckler · Accepted solution]

@dodiyal I did it this way. PBIX is attached below signature.

Burndown = 
    VAR __Week = MAX('Data 1'[week number graph])
    VAR __Customers = DISTINCT(SELECTCOLUMNS('Data 2',"__Customer",[Customer Group]))
    VAR __Table = FILTER(ALL('Data 1'),'Data 1'[Customer Group] IN __Customers && [week number graph] <= __Week)
    VAR __Total = SUMX(FILTER(ALL('Data 1'),'Data 1'[Customer Group] IN __Customers),[No. of Lines])
    VAR __SoFar = SUMX(__Table,[No. of Lines])
    VAR __Result = MAX(__Total - __SoFar, 0)
RETURN
    __Result

[dodiyal]

Thank you Greg, you are awesome 😊