cancel
Showing results for
Did you mean:
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

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

1 ACCEPTED SOLUTION
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.
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.

Announcements

#### 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.

#### Check it Out!

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

#### The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors