Legend wont work with SUM Function

[BaileyL]

Jobs

I have a table of jobs that begin and end on certain days, each job has a department 

Date Table

I have a datetable that spans the min/max of the jobs days. I have a calculated column (Total_Daily_Value) that works out the total ($) for that day, this is done by looking at wether the date falls between the beginning and end date of each job. I sum the filtered Job table to get the daily value. 

Total_Daily_Value =

SUMX ( FILTER ( JOBS,

        JOBS[DATE_COMPLETE] >= '0_TIMETABLE'[DATE]+[M_CLOSING_TIME] &&
        JOBS[DATE_SCHEDULED] <= '0_TIMETABLE'[DATE]+[M_CLOSING_TIME]),
        JOBS[C_JOB_TOTAL_PRICE]

)

Departments 

I have a department table that has a '1 to Many' relationship to the JOBS table. 

My graph's X axis is [DATE] from Date Table. My Y axis is the (Total_Daily_Value). When I use the department as the legend, it is unable to determain the values for each department. For example

Date, Total_Daily_Value
27/08/2024, $45000                 (Job1 $8000, Job2 $25000, Job3 $12000)

The graph shows $45000 three times with each 'Job' represented as $45000, instead of its actual amount. 

Thanks,

Bailey. 

[Anonymous]

Hi, @BaileyL 

Based on your description of your requirements, we believe that the problem is caused by an unclear table relationship. Because there is no specific data, we can only give the following solutions, which we hope will be helpful to you:

1.First, you can use the lookupvalues() function to reference the job column you need to your Date Table table, and then create the visualization as normal.

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn
 

2.Second, you can also try the following measure to see if that solves your problem:

Total_Daily_Value =

SUMX (

    FILTER (

        JOBS,

        JOBS[DATE_COMPLETE] >= '0_TIMETABLE'[DATE] + [M_CLOSING_TIME] &&

        JOBS[DATE_SCHEDULED] <= '0_TIMETABLE'[DATE] + [M_CLOSING_TIME] &&

        JOBS[DEPARTMENT] =max(Departments[Department])

    ),

    JOBS[C_JOB_TOTAL_PRICE]

)

3.Finally, to return to the root of the problem, it is recommended that you organize your data structure and adjust the table relationships to see if this problem can be solved:
Relationship troubleshooting guidance - Power BI | Microsoft Learn
We recommend that you create your data using a star structure:

 Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
 

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community
 

Please be careful to remove sensitive information and protect your important information.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BaileyL · Accepted solution]

Hi, 

Thanks for your help. Unfortunatly the modified measure didn't work, instead I found the solution by doing these two things:

replace JOBS with ALL(JOBS)

and after the filter:

KEEPFILTERS(RELATEDTABLE('Departments'))

Thanks,

Bailey.