Forum Discussion

jorgus's avatar
jorgus
Frequent Visitor
7 years ago

Sales vs Sales Target

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.


 

 

 

Need Help

3 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    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's avatar
      jorgus
      Frequent Visitor
      7 years ago

      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's avatar
        jdbuchanan71
        Super User
        7 years ago

        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.