Sales vs Sales Target

[jorgus]

Hello everyone.
I have a group of sales tables, related to each other, and I have a table of sales objectives, which is not related to any table but has keys from several tables.

These are the sales and related tables

And this is the table of sales targets, in this table not all columns need to have data. Año(Year), IdSucursal (Branch), IdRubro (Kind of category) and Importe (Amount) are required, the others are optional.

This is the view of all tables

If possible, I need to create a measure that shows me the budget filtered by several values, such as year, branch, and several categories dynamically.

Something like this:

I have tried several ways to create some measure but none with the expected result, this is the one that is closest to what I need, but it is not correct either.

Objetivo.Anual. Importe Anual = 
CALCULATE (
    SUM ( Dim_Objetivo[Importe] )
	,IF ( HASONEVALUE ( DIM_CALENDARIO[Año] ), (Dim_Objetivo[AÑO] = VALUES ( DIM_CALENDARIO[Año] )), TRUE() ) 
    ,IF ( HASONEVALUE ( Dim_Sucursal[IdSucursal] ), (Dim_Objetivo[IdSucursal] = VALUES ( Dim_Sucursal[IdSucursal] )), TRUE()) 
	,IF ( HASONEVALUE ( Dim_Producto[IdRubro]), (Dim_Objetivo[IdRubro] = VALUES ( Dim_Producto[IdRubro] )), TRUE() ) 
    ,IF ( HASONEVALUE ( Dim_Producto[IdLinea] ), (Dim_Objetivo[IdLinea] = VALUES ( Dim_Producto[IdLinea] )), TRUE() )
	--,IF ( HASONEVALUE ( Dim_Producto[IdGrupo] ), (Dim_Objetivo[IdGrupo] = VALUES ( Dim_Producto[IdGrupo] )), TRUE() ) 
)

I attached the file to facilitate interpretation, I apologize that it is in Spanish.

I hope you can help me and I congratulate this great community that with their contributions has already helped me solve several problems.

[jdbuchanan71]

Hello @jorgus 

Try these two measures.

The first one is just to sum the target

SumOfImporte = SUM ( Dim_Objetivo[Importe] )

This next one takes the target and applies the filtering from the values selected for the other columns

Budget Amount =
VAR YearList =
    VALUES ( Dim_Calendario[Año] )
VAR SucursalList =
    VALUES ( Dim_Sucursal[IdSucursal] )
VAR RubroList =
    VALUES ( Dim_Producto[IdRubro] )
VAR LineaList =
    VALUES ( Dim_Producto[IdLinea] )
VAR GrupoList =
    VALUES ( Dim_Producto[IdGrupo] )
RETURN
    CALCULATE (
        [SumOfImporte],
        TREATAS ( YearList, Dim_Objetivo[Año] ),
        TREATAS ( SucursalList, Dim_Objetivo[IdSucursal] ),
        TREATAS ( RubroList, Dim_Objetivo[IdRubro] ),
        TREATAS ( LineaList, Dim_Objetivo[IdLinea] ),
        TREATAS ( GrupoList, Dim_Objetivo[IdGrupo] )
    )

[jorgus]

Good morning @jdbuchanan71 , thanks for you answer.

With the function treadas it works perfectly, it was what I needed.

The problem I still have is when in my budget table a column has no value, for example IdGrupo, when a value is missing, the measure returns nothing since it expects the value of the GroupID.

[jdbuchanan71]

If the column has not value on a row how would PowerBI know which lines to assign it to?  That is how it is supossed to behave.