Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am working on a dashboard that can track all the projects that are currently being undertaken. The aim is to report on the progress of the various projects over a period of time. The data is being sourced from monthly excel files that are filled in manually (which is a major pain point as it is!). Over time, projects status change. For example, if in the August 2018 report a project is closed, it is not reported in the September 2018. Kind of like an inventory of items
I'll try and explain the dataset if it helps. There are numerous columns that contain various project parameters such Project Status, which is RED, AMBER, and GREEN; Project Stages, delivery, initiaite, close, etc; Project Assurance Levels, amongst others. The data structure is further complicated by additional four funding columns- Financial Year1, Year2, Year3, Year4 funding. Due to this structure, I have to unpivot the funding year columns because of which there are 4 additional rows for each and every single project. Multiplying that with the different reporting cycles just makes the report extremely vast, with repeated information for other static columns
I have managed to figure out how to obtain all the projects being reported in a specific reporting period using the following code (if there is a better way to do it, I would appreciate that)
Total number of Initiatives = CALCULATE ( DISTINCTCOUNT ( Project[Project ID] ), FILTER ( 'Calendar', 'Calendar'[Date] = MAX ( Project[Report As At] ) )
The formula I have used to calculate the actual cost to date is
Cost to date Using SUMMARIZE and SUMX
Cost to date Using SUMMARIZE and SUMX = CALCULATE( SUMX( SUMMARIZE(Project,Project[Project ID],Project[Cost to date]),Project[Cost to date]), FILTER( Project,Project[Report As At]=MAX(Project[Report As At]) ))
Again, if there is a better way of doing it, do let me know. There are other expenditure columns such a original expenditure, revised expenditure, etc, that are not shown here. The actual cost to date is what changes every month as the project progresses
Looking at the image below, I am able to get the correct number of projects being reported in a reporting period, 3 in this case as there are 3 projects being reported in the September report. But, the doughnut chart shows 4 projects. According to the table visual, the red project is Project Name 3, which was reported in 31/08/2018
But in the latest reports, it progressed to Amber. Hopefully, the image below will provide more clarification. Selecting Project 3 should have changed to visual to show only AMBER as the status and Initiate as the project stage
How do I go about showing the correct number for a reporting period. Do note that there are other columns in the actual dataset that are usually static but can possibly change as well. But the ones that change the most frequently are Project Status, Stage,Project Explanatory notes (not shown in this example)
Report As At Project ID Project Name Project Stage Finances as At Cost to date Status Funding Year1 Funding Year 2 Funding Year 3 Funding Year 4 30/09/2018 Project-ID1 Project Name 1 Delivery 100000 Green 10000 0 0 0 30/09/2018 Project-ID2 Project Name 2 Closed 150000 Green 25000 0 0 0 30/09/2018 Project-ID3 Project Name 3 Initiate 200000 Amber 30000 35000 0 0 31/08/2018 Project-ID1 Project Name 1 Delivery 75000 Amber 10000 0 0 0 31/08/2018 Project-ID2 Project Name 2 Initiate 120000 Green 25000 25000 0 0 31/08/2018 Project-ID3 Project Name 3 Paused 70000 Red 30000 0 0 0 31/08/2018 Project-ID4 Project Name 4 Closed 5000 Green 0 0 0
I have added a sample dataset above as I couldn't upload the excel and PBI file
Any help will be truly appreciated
Thank you
hi, @Anonymous
It seems that the data you provide is a bit confusing, could you please provide a simple sample pbix file and the expected output
You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
hi..was wondering if you had any suggestions for me and if you are able to access the files
Thanks
hi, @Anonymous
Sorry for late reply, There were a lot of emails in the mailbox that I didn't notice this.
I'm not able to access the files from your link, but I use the data in the picture do a test.
Here I explain your confusion:
First
Total number of Initiatives = CALCULATE ( DISTINCTCOUNT ( Project[Project ID] ), FILTER ( 'Calendar', 'Calendar'[Date] = MAX ( Project[Report As At] ) )
This formula calculate how many the projects being reported in a specific reporting period
The result is different between total and the sum of row result is involved the row context and filter context .
the result in the row of visual is calculated according to the current line and the total is calculated according to the whole table
for example:
Since you don't select any Report As At from slicer.
for each row in the lower left corner of table visual, Max( Project[Report As At ])="Calendar"[Date] , so Total number of Initiatives is 1 for each row, but for the total, Max( Project[Report As At ]) is 2018-09-30, so It just calcuate the date is "2018-09-30" and the result is 3.
And the table visual in the top right corner, it is an aggregate summary table visual for the table visual in the lower left corner.
and if you select the doughnut chart and change it to table visual, you will find it is the same visual as the table visual in the top right corner.
Second
You may try to use this formula to add a measure
Measure = IF(CALCULATE(MAX(Project[Report As At ]),ALLSELECTED(Project))=MAX(Project[Report As At ]),1,2)
then add it into the visual level filter and set filter result is "1" as below:
Here is my demo pbix, please try it.
All your confusion comes from the row context and filter context in DAX.
Sorry again for the late reply.
Best Regards,
Lin
Thanks for your response, Lin
I realised that it was something to do with my filter context. Just wasn't sure how to resolve it. Could you please explain the logic behind this code -
Measure = IF(CALCULATE(MAX(Project[Report As At ]),ALLSELECTED(Project))=MAX(Project[Report As At ]),1,2)
This requires that the above measure to be added to the report filter and select 1 for every report on the page, correct? I wasn't abe to add this measure at the Page or Report level. The status is just one of the pie chart. There are more such as the Project Stage
I was having a go at this myself and not sure if there is a better way of doing it
I created a calculated column ISLatest which says looks at the Report As At date and marks it as "Latest" or "Not Latest" (I think this is a redundant step and can be avoided)
Then, I created the below measure
Measure WIP = IF( ISFILTERED(Project[Report As At]),[Total number of Initiatives], CALCULATE( [Total number of Initiatives], FILTER('Calendar','Calendar'[Date]=MAX(Project[Report As At])), FILTER(Project,Project[ISlatest]="Latest")) )
Then I ended up with this which is sort of what I wanted to achieve
I am unable to paste an image for some reason. I just can't paste it like the way I did in my initial post
Posting the image of what I ended up with
Any suggestions? If there is a better way of doing it, I would really appreciate as this seems like a convoluted way of doing it
Thanks again!
hi, @Anonymous
First
I wasn't able to add this measure at the Page or Report level. It is only can be added into visual level filter, not Page or Report level
Second
Measure = IF(CALCULATE(MAX(Project[Report As At ]),ALLSELECTED(Project))=MAX(Project[Report As At ]),1,2)
CALCULATE(MAX(Project[Report As At ]),ALLSELECTED(Project)) returns the max Project [Report As At], It's going to have the same effect as calculated column ISLatest
And measure is dynamic and column is static.
for example:
If you only select Project Name 4, column still returns 2018-09-30 but my measure will return 2018-08-31
and it will lead to error result.
Best Regards,
Lin
Thanks for that. So, I need to add Measure and filter it to 1 for every visual in the report
hi, @Anonymous
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Best Regards,
Lin
Hi Lin,
Thanks for your help with this. I am having another issue summing up Cost to Date. The formula I have used is
Cost to date Using SUMMARIZE and SUMX = CALCULATE( SUMX( SUMMARIZE(Project,Project[Project ID],Project[Report As At],Project[Cost to date]),Project[Cost to date]), FILTER( 'Calendar','Calendar'[Date]=MAX(Project[Report As At]) ))
One of the measures I am trying to work out is the Cost to date of all the projects that have been closed so far. The
The result that I expect is the Cost to dates ofr all the projects that have been closed so far, which is $165,000. But instead, I am getting the cost to date of the Project which has the latest Report As At date. In this case Project 2, $150,000
Truly appreciate any help
hi, @Anonymous
This is a measure totals problem. Very common. See these two posts about it here:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Best Regards,
Lin
hi, @Anonymous
For your requirement, it needs you to add Measure and filter it to 1 for every visual in the report.
Because if you don't select any date from Report As At, the calculation is calculated for the whole table. Unless you set the slicer must select at least one value.
Best Regards,
Lin
Thanks for your reply
Can you please try and access this link? I have never tried sharing a link outside of my office network
Basically, when the"report as at" slicer is unselected (refer image above), the visual ideally must display the status of only 3 projects as one of them closed in the previous reporting period and is no longer required in the current cycle. Likewise, selecting a past reporting cycle, such as 31/08/2018, should display all 4 projects
So if a project is selected, it should display the status for the current reporting period. The problem I am facing is that, selecting a single project, e.g Project 3 which was reporting RED in August and AMBER in September, shows both the categories in the visual (as below) instead of only AMBER as that was what Project 3 was reported in September
Below is a screenshot of the raw data incase you cannot access the link. There isn't any confidential information in the file
Hope that helped
Thank you
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |