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

[jlayala]

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

example data is:

log table

Date_transaction	id	nombre	monto	producto_sku
08/10/21	158975	GRUPO QUATRO	$389.00	8469760000019
28/10/21	165671	GRUPO QUATRO	$759.00	8469760000149
04/11/21	166792	GRUPO QUATRO	$222.00	8469760000293
08/11/21	168090	GRUPO QUATRO	$406.00	8469760000293
09/11/21	168648	GRUPO QUATRO	$389.00	8469760000019
30/11/21	182791	GRUPO QUATRO	$1,949.00	8469760000149

 

client

id	name
22	Eli Lilly
44	Grupo Quatro
3	Critoinvex SAPI DE CV
4	WORKBEAT
5	Sodexo

Comisions

Nombre de Producto	SKU	From_date_valid	thru_date_valid	Fix_rate_cargo_paynom	percent_rate_cargo_paynom	Fix_rate_comision	percent_rate_comision	cliente_id	Menor_igual_0	Menor_igual_1	Menor_igual_2	Mayor_que
TELMEX	8469760000019	01/01/21	31/12/21	5		10		22	300
CFE	8469760000149	01/11/21	31/12/21	5		10			300
AGUA Y DRENAJE DE MTY	8469760000293	01/01/21	31/12/21	3		7		44	300
TELMEX	8469760000019	01/11/21	31/12/21	5	3%	13		22				300
TELMEX	8469760000019	01/01/21	31/10/21	1	10%	5		22				300
CFE	8469760000149	01/11/21	31/12/21	5	3%	10						300
AGUA Y DRENAJE DE MTY	8469760000293	01/01/21	31/12/21	25	3%	13		44				300

 thanks

[jlayala · Accepted solution]

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.