Calculate Goal by Category and Year

[Syndicate_Admin]

Estimated,

I have the following query regarding the calculation of a goal fulfillment by category type and year.

The DAX functions to be used would be the following:

Meta = CALCULATE(SUM('Metas Sinergia'[META]),

TREATAS(VALUES('Metas Sinergia'[DATE]),'020 Base DL'[Actual end date]))

Where: Goals Synergy is the table whose fields are Year, Category and Goal.

Compliance is a Divide between F. Launches and Goal noted above.

% Compliance = DIVIDE([F. Releases],[Meta])

F. Releases = CALCULATE(SUM('031 QRY PT+DL per Release'[Z_Validador_JP2]), FILTER('031 QRY PT+DL per Launch',LEFT('031 QRY PT+DL per Launch'[Description],11) = "Release"))

The result when displayed in table:

The problem is that the DAX of F. Releases shows me an inconsistent number (58). Unfortunately to solve this, I would have to apply a relationship of tables between Synergie Goals and the Base Table that will ruin the other formulas and configurations that I have in the report.

I stay tuned.

Best regards!

[Syndicate_Admin]

Estimated,

I made an adjustment by adding new tables plus relationships, and it has improved the situation (the 58 is no longer repeated in all rows and columns as before).

The problem is that the total 58 (which is the sum of all the years) with their respective subtotals is repeated in all the columns.

What is the reason for this problem? (you have to improve a relationship, badly placed, etc.).

Best regards!

[v-yangliu-msft]

Hi  @Syndicate_Admin ,

Do you mean that all 58 are incorrect or is the 58 above Total incorrect?

If it's the first one, you can try using the all() function

If it's the second one, you can use the HASONEVALUE function.

Measure =
var _table1=
SUMMARIZE('TABLENAME',' TABLENAME[Messa Sinergia]'[F. Releases],"Value",[Varejo 4 Sem])
return
IF(
    HASONEVALUE(TABLENAME[Messa Sinergia])
    [F. Releases],
    SUMX(_table1,[Value]))

Best Regards,

Liu Yang

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

[Syndicate_Admin]

Estimado Liu Yang,

Thanks for the answer, I have tried to replicate it in my model but I have not succeeded. In addition to the above, I had already tried to make a SUMMARIZE, but the part of relating it to annual goals complicates the formula.

I have developed an excel example that would illustrate my case better, and that more than one who works in the commercial area and calculates sales could be useful.

Sales table:

Goals table:

And based on those two tables, get this final:

Greetings and thanks!