Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All
I am attempting to display summary lines in a PBI table for projects (these are pulled out of a simple sharepoint list) and then also include associated data from other tables held in our planning system (most of which is just drawn out of excel).
So...in it's simplest form I have a 'projects' table as follows:
Prj Code, Year, Month, Audit RAG, Status RAG, Notes
XX01 2018 1 RED RED
YY01 2018 1 GREEN AMBER
YY01 2018 2 GREEN GREEN
YY01 2018 3 AMBER AMBER
I also have data held in associated tables such as forecast, revenue, commercials
All these 'associated' tables have the same format
Prj Code, Date, Value
YY01 01/01/2018 1098.28
YY01 02/01/2018 2083.27
YY01 05/01/2018 736.55
YY01 01/02/2018 8731.33
YY01 02/02/2018 1092.33
XX01 01/03/2018 3928.22
I just want to display the results in a table which can be filtered by Project / Year / Month...ideally without the need for master tables (i.e. calendars or project headers etc).
The project summary is by period. The associated data is by day.
I'd like to display something like this....
Prj Code, Year, Month, Audit RAG, Status RAG, Prd Forecast, **bleep**. Forecast, Prd Revenue, **bleep**. Revenue, Commercials
XX01 2018 1 RED RED £10294.33 £10294.33 £11928.33 £11928.33 £150726.33
YY01 2018 1 GREEN AMBER £9827.33 £9827.33 £12938.44 £12938.44 £120726.33
YY01 2018 2 GREEN GREEN £8928.33 £18755.66 £10928.22 £23866.66 £120726.33
YY01 2018 3 AMBER AMBER £7255.01 £26010.67 £9287.44 £33154.10 £120726.33
The cumulative values are running totals for the project, irrespective of the selected year/period.
At first sight it looks pretty simple but I've had loads of trouble trying to model the data and I'm not an expert in DAX.
Any help would be greatly appreciated.
first of all I would chagne the data model:
1) add calendar table
2) create project list table with unique projects only
3) change the month granlarity to 1st day of the month in Projects table
4) optional - aggregate 'associated' tables into 1 in Power Query
then I would join the 1 & 2 with Projects and either aggregated data or individual tables
as long as Prj Code, Year, Month would come from tables 1&2 they will propagate filter context to underlying data tables, being able to produce the output you expect
Hi - Thanks for the reply
I've tried your suggestion by creating a calendar and project master table to drive the data however I am still struggling to get the report slicers to work as I'd expect. I think it may be something to do with the measure I use on the table.
To try and make it clearer - here is my data model.
TimeByDay in Prj_Status table is set to 1st day of month. It is linked to the Calendar on this column and also to Prj_Master on ParentProject column. There will potentially be a row for each combination of project/year/month.
Assignments has thousands of rows for all projects (not all projects are in the Prj_Status table) and each row has a ParentProject and TimeByDay which is how it is joined to the two driving tables (Prj_Master and Calendar).
I have created an independent Measures Table to hold calculated measures that I want to show revenue (which is held in Assignments) and need it to be summarised by project/year/month.
So...in this example I have 4 rows in my Prj_Status table Jan-Mar for the first project and April for the second.
All the data in the matrix is derived from Prj_Status except the measures which is a SUMX of revenue in assignments.
ThisMonth + 0 should show revenue by Project / Year / Month number
ThisMonth + 1 shoud show revenue for next month and ThisMonth + 1 should show revenue for two months ahead.
Instead these columns are showing all revenue for both projects for all periods.
I have tried various CALCULATE DAX() formula with FILTERS etc. but can't find a way to get this to work.
what formulas do you have for the measures?
From what I see the syntax should be quite simple, like:
'This Month + 0' = SUM('Assigments[Value])
maybe wrapping it into calculate to add filter on revenue/forecast etc.
I first calculate revenue as a column on the Assignments table:
Revenue = Assignments[AssignmentWork]*Assignments[Tasks.Probability]*Assignments[Projects.HourlyRate]
My measure is then simply:
ThisMonth + 0 = SUM(Assignments[Revenue])
With the measures added to the table neither of the two slicers have any effect. I have to remove the measures before the filtering works. It's really weird.
There is no direct link in my visual table between the columns (all of which come from Prj_Status) and my measures (which are calculated from assignments).
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |