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

using greater than in a calculate measure do not bring the right outcome

Hi everyone, 

I am lookin for some advice. I create de following measure that seems not working: 

Venta Cargo Paynom comision mayor que Fija =
VAR Fecha =
SELECTEDVALUE ( 'paynom log_pagos'[Date_transaction] )
VAR comision_mayor_que =
CALCULATE (
MAX ( 'Producto_sku_Fees (venta)'[Fix_rate_cargo_paynom] ),
FILTER (
'Producto_sku_Fees (venta)',
'Producto_sku_Fees (venta)'[From_date_valid] <= Fecha
&& 'Producto_sku_Fees (venta)'[thru_date_valid] >= Fecha
&& [Importe solicitado] > 'Producto_sku_Fees (venta)'[Mayor_que]
)
)
RETURN
comision_mayor_que
The other measeures are like the above but instead of grether than is less or equal than, and "venta carga paynom sku fija" is the sum of all. 
The expected outcome for "venta cargo paynom comision mayor que" is  0 when "importe" <= $300. (rows 7 and 9 in the picture table)
Venta Cargo Paynom comision menor_igual_0 Fija =
VAR Fecha
SELECTEDVALUE ( 'paynom log_pagos'[Date_transaction] )
VAR comision_menor_igual_0 =
CALCULATE (
MAX ( 'Producto_sku_Fees (venta)'[Fix_rate_cargo_paynom] ),
FILTER (
'Producto_sku_Fees (venta)',
'Producto_sku_Fees (venta)'[From_date_valid] <= Fecha
&& 'Producto_sku_Fees (venta)'[thru_date_valid] >= Fecha
),
FILTER (
'Producto_sku_Fees (venta)',
[Importe solicitado] <= 'Producto_sku_Fees (venta)'[Menor_igual_0]
)
)
RETURN
comision_menor_igual_0
 
Captura de Pantalla 2022-01-05 a la(s) 9.15.19.png
example data is:
log table
Date_transactionidnombremontoproducto_sku
08/10/21158975GRUPO QUATRO$389.008469760000019
28/10/21165671GRUPO QUATRO$759.008469760000149
04/11/21166792GRUPO QUATRO$222.008469760000293
08/11/21168090GRUPO QUATRO$406.008469760000293
09/11/21168648GRUPO QUATRO$389.008469760000019
30/11/21182791GRUPO QUATRO$1,949.008469760000149
 
client
idname
22Eli Lilly
44Grupo Quatro
3Critoinvex SAPI DE CV
4WORKBEAT
5Sodexo
 
Comisions
Nombre de ProductoSKUFrom_date_validthru_date_validFix_rate_cargo_paynompercent_rate_cargo_paynomFix_rate_comisionpercent_rate_comisioncliente_idMenor_igual_0Menor_igual_1Menor_igual_2Mayor_que
TELMEX846976000001901/01/2131/12/215 10 22300   
CFE846976000014901/11/2131/12/215 10  300   
AGUA Y DRENAJE DE MTY846976000029301/01/2131/12/213 7 44300   
TELMEX846976000001901/11/2131/12/2153%13 22   300
TELMEX846976000001901/01/2131/10/21110%5 22   300
CFE846976000014901/11/2131/12/2153%10     300
AGUA Y DRENAJE DE MTY846976000029301/01/2131/12/21253%13 44   300
 
 thanks
 
 
1 ACCEPTED SOLUTION
jlayala
Frequent Visitor

HI again! 

thinking different options and disaggregating the fromula I discovered that what causes that I did not get the calculation I wanted was blanks in data. Going thru each section or filter was the way a discovered. This blanks caused that the calculation of greater than was compare to 0 when in reality I wanted this blanks not to be taked into account (be filtered first).

I change the measure to: 

comision_mayor_que_general =
CALCULATE (
MAX ( 'Producto_sku_Fees (venta)'[Fix_rate_cargo_paynom] ),
FILTER (
'Producto_sku_Fees (venta)',
'Producto_sku_Fees (venta)'[From_date_valid] <= fecha
&& 'Producto_sku_Fees (venta)'[thru_date_valid] >= fecha
&& 'Producto_sku_Fees (venta)'[nombre cliente] = BLANK()
&& 'Producto_sku_Fees (venta)'[Menor_igual_0] = BLANK()
&& 'Producto_sku_Fees (venta)'[Mayor_que] < [importe solicitado]
&& ISNUMBER('Producto_sku_Fees (venta)'[Mayor_que]) = TRUE()
)
)
RETURN
 
Hope this example helps other.

View solution in original post

1 REPLY 1
jlayala
Frequent Visitor

HI again! 

thinking different options and disaggregating the fromula I discovered that what causes that I did not get the calculation I wanted was blanks in data. Going thru each section or filter was the way a discovered. This blanks caused that the calculation of greater than was compare to 0 when in reality I wanted this blanks not to be taked into account (be filtered first).

I change the measure to: 

comision_mayor_que_general =
CALCULATE (
MAX ( 'Producto_sku_Fees (venta)'[Fix_rate_cargo_paynom] ),
FILTER (
'Producto_sku_Fees (venta)',
'Producto_sku_Fees (venta)'[From_date_valid] <= fecha
&& 'Producto_sku_Fees (venta)'[thru_date_valid] >= fecha
&& 'Producto_sku_Fees (venta)'[nombre cliente] = BLANK()
&& 'Producto_sku_Fees (venta)'[Menor_igual_0] = BLANK()
&& 'Producto_sku_Fees (venta)'[Mayor_que] < [importe solicitado]
&& ISNUMBER('Producto_sku_Fees (venta)'[Mayor_que]) = TRUE()
)
)
RETURN
 
Hope this example helps other.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors