cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jorgus Frequent Visitor
Frequent Visitor

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

Sales.png

 

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.
Targets.png

 

This is the view of all tables

All_Tables.png

 

 

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:

Report.png

 

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.


 

 

 

3 REPLIES 3
Super User
Super User

Re: Sales vs Sales Target

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 Frequent Visitor
Frequent Visitor

Re: Sales vs Sales Target

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.


Super User
Super User

Re: Sales vs Sales Target

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 147 members 1,928 guests
Please welcome our newest community members: