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.
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 |
Solved! Go to 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
Hi,
Would it be possible to show the expected result in another column of Table1?
@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
Thank you Greg, you are awesome 😊
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |