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.
I have IT Tickets data with below shown columns
I would like to create a report like below...
Month | Created Count | Resolved Count | Backlog | Calculation |
100 | <- Backlog | |||
Jan-19 | 50 | 40 | 110 | <- 100+50-40 |
Feb-19 | 100 | 80 | 130 | <- 110+100-80 |
Mar-19 | 200 | 100 | 230 |
I have the raw data and PBIX in one drive for you review.
https://1drv.ms/u/s!AuZ8zsEu-lf-aKZZdq9ll8lX0JA?e=Z93Vf4
Problem is I have done this in Tableau and QlikView in just a right click on mouse. But I have no clue on how to achieve these three metrics in a single table in Power BI. I have the similar requirement to show the same table by IT Team list instead of Month list etc.,
For the above I have a perfect solution through
Below shown is the screenshot of solution which is correct.
BUT when i filter to select only FEB MAR APR. The backlog count is showing wrongly.. What is the reason and how to correct this?
Solved! Go to Solution.
Hi @Anonymous ,
You can try to use following measure formulas if it works:
cCreated = CALCULATE ( COUNT ( data[ID] ), FILTER ( ALLSELECTED ( data ), FORMAT ( [Created], "mm/yyyy" ) = FORMAT ( min ( 'Table'[Date] ), "mm/yyyy" )&&[Created]<>BLANK() ) ) cResolved = CALCULATE ( COUNT ( data[ID] ), FILTER ( ALLSELECTED ( data ), FORMAT ( [Resolved], "mm/yyyy" ) = FORMAT ( min ( 'Table'[Date] ), "mm/yyyy" )&&[Resolved]<>BLANK() ) ) cUnsolved = [cCreated]-[cResolved] BackLog = VAR currDate = MIN ( 'Table'[Date] ) VAR prev = SUMX ( SUMMARIZE ( FILTER ( ALL ( data ), [Created] < currDate ), [Created].[Year], data[Created].[Month], "Count", COUNT ( data[ID] ) ), [Count] ) - SUMX ( SUMMARIZE ( FILTER ( ALL ( data ), [Resolved] < currDate ), [Resolved].[Year], [Resolved].[Month], "Count", COUNT ( data[ID] ) ), [Count] ) RETURN prev + [cUnsolved]
Regards,
Xiaoxin Sheng
Hi @Anonymous ,
You can try to use following measure formulas if it works:
cCreated = CALCULATE ( COUNT ( data[ID] ), FILTER ( ALLSELECTED ( data ), FORMAT ( [Created], "mm/yyyy" ) = FORMAT ( min ( 'Table'[Date] ), "mm/yyyy" )&&[Created]<>BLANK() ) ) cResolved = CALCULATE ( COUNT ( data[ID] ), FILTER ( ALLSELECTED ( data ), FORMAT ( [Resolved], "mm/yyyy" ) = FORMAT ( min ( 'Table'[Date] ), "mm/yyyy" )&&[Resolved]<>BLANK() ) ) cUnsolved = [cCreated]-[cResolved] BackLog = VAR currDate = MIN ( 'Table'[Date] ) VAR prev = SUMX ( SUMMARIZE ( FILTER ( ALL ( data ), [Created] < currDate ), [Created].[Year], data[Created].[Month], "Count", COUNT ( data[ID] ) ), [Count] ) - SUMX ( SUMMARIZE ( FILTER ( ALL ( data ), [Resolved] < currDate ), [Resolved].[Year], [Resolved].[Month], "Count", COUNT ( data[ID] ) ), [Count] ) RETURN prev + [cUnsolved]
Regards,
Xiaoxin Sheng
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |