Can't Get KPI Visual to work with Multiple Tables

[PatrickMcLeanGL]

I have a table "Current Data" that contains Labour Hours and Name, and receives multiple entries per day per name (10 line items might have 1 hour each, so total time for that person for the day would be 10 hours) and have another table "Available Hours" that lists the goal hours for that person (so goal hours for that person who worked 10 hours might have been 11 for example). Lastly I have my Date Table with relationships to the date columns on each of these other two tables, and I have a relationship between the Name columns of Current Data and Available Hours.

However I can not get the KPI card to work. My Value is the sum of labour hours for one particular user, the Trend Axis is the Date from Date Table, and the Target is the Sum of the Hours Available for that user. 

I can either get the Value # correct, or the Target # Correct, but not both.

[SVADDE21]

Hello,

This will work as well.

TotalGoalHours = SUM('Available Hours'[Goal Hours])

SelectedGoalHours = CALCULATE(
    [TotalGoalHours],
    ALLSELECTED('Date Table'[Date])
)

TotalLabourHours = SUM(CurrentData[Labour Hours]) 

SelectedLabourHours = CALCULATE(
    [TotalLabourHours],
    ALLSELECTED('Date Table'[Date])
)

In KPI Visual, Place SelectedGoalHours in Target, SelectedLabourHours in Value, Date from Date Table in Trend Axis.

[v-bmanikante]

Hi @PatrickMcLeanGL ,

Thank you for reaching out to Microsoft Fabric Community Forum.

@Ashish_Mathur @DataInsights Thank you for your quick response.

@PatrickMcLeanGL ,I have created a sample Power BI file and included a KPI visual based on the sample data. Please review the file and confirm if it meets your expectations. If not, kindly share your sample data along with the desired output, so I can make the necessary adjustments accordingly.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

[PatrickMcLeanGL]

So I had mine set up exactly like that, but it doesnt work when a date range is picked. So if I had my date selected as 2 days, I would want the sums of each value compared. Even in the PBIX you shared, it only shows the most recent date's value if both dates are selected. Ideally I actually dont want an external filter selection, I want the filter baked into the visual because I'm going to have a KPI for each employee. So in your example, I'd want 2 KPI visuals, one for John and one for Alice, with a date slicer, and if I picked both dates April 1 and 2, for Alice it should show 11/13 (5/6 + 6/7) and Johns would show 15/17 (10/11 + 5/6).

You can see here that for March 21st and April 3rd (single dates), it works and its accurate.

But when I select a range between the two dates, it only shows most recent data (from April 3rd) in the KPI card, it should show the values in the two "Card" visuals to the left: 

Heres my data:

I want the KPI to show the total value for the date range.

[PatrickMcLeanGL · Accepted solution]

I actually solved this just now, using a new visual calculation and calculating the RUNNINGSUM. 

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result in a Table format.  Share data in a format that can be pasted in an MS Excel file.

[DataInsights]

@PatrickMcLeanGL,

Try creating a Person table that contains all names in the other two tables. Then create a relationship between the Person table and each of the other two tables (Current Data and Available Hours). It's best to avoid joining fact tables together (join dimension tables to fact tables).