Summing values of one column and comparing it to a column from another table - Direct Query

[Anonymous]

Hi, first of all, I'm using the Direct Query mode and my trouble concerns these two tables:

First Table contains the Dates, Teams, Activities and Price of each actitivity that was executed (the sum is the daily productivity).

The Second Table is a helper table that contains Dates, Teams and the Daily Goal ($) to achieve (it varies accordingly with the day of the week).

My purpose is quite simple, actually, i just want to SUM the values of the column preco_a_cobrar (productivity) and compare this value to the values  of meta_diaria (daily goal).

As I said, I'm using the Direct Query Mode and the intrinsic limitation is the reason of my struggling.

Can someone, please, help?

Best Regards,

Marcio.

[Anonymous]

(Generated from Chat Gpt)

To compare the sum of the productivity (preco_a_cobrar) to the daily goals (meta_diaria), you can use a measure in Power BI. Here's an example measure that calculates the total productivity for a selected date and team

Total Productivity =
CALCULATE(
SUM('Table 1'[preco_a_cobrar]),
ALLEXCEPT('Table 1', 'Table 1'[Data], 'Table 1'[Equipe])
)

This measure uses the CALCULATE function to sum the values in the preco_a_cobrar column for a selected date and team. The ALLEXCEPT function removes any filters on the Data and Equipe columns, so that the calculation is based only on the selected date and team.

To compare the total productivity to the daily goal, you can create another measure that calculates the difference between the two values

Productivity vs. Goal = [Total Productivity] - MIN('Table 2'[meta_diaria])

This measure subtracts the daily goal (from Table 2) from the total productivity (calculated by the previous measure). The MIN function is used to get the daily goal value for the selected date and team.

You can then create a visual that shows the daily goal and the total productivity, and use the second measure to show the difference between the two values. For example, you could create a clustered column chart with the date on the X-axis, the daily goal and total productivity as separate values in the Values field, and the Productivity vs. Goal measure as a secondary value in the same field.

[Anonymous]

Hi, I really appreciate your answer but, unfortunately, this solution didn't help much. I thought about using a MAX/MIN but I forgot to mention that I also need to SUM the values of the meta_diaria (daily goal) because I want to  add a slicer with the Dates so the values can be changed dinamically.