Forum Discussion
Subtracting values in same column until the value becomes 0
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 |
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
3 Replies
- Greg_Deckler
Community Champion
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 __ResultThank you Greg, you are awesome 😊
- Ashish_Mathur
Super User
Hi,
Would it be possible to show the expected result in another column of Table1?
