Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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] ) )
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |