Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AndyTrezise
Helper II
Helper II

Using CALCULATE on unrelated table

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.

5 REPLIES 5
Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.

 

a.png

 

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.

c.png

 

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.



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

d.png

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).

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.