I need to calculate hours amount for unique routes in specific condition:
IF(8 - [Amount] <= 0, 0, [Amount])
Measure [Amount] is obtained from the row context measure SUMX, but applying IF statement to this measure it turns into a filter context and doesn’t snow totals in table.
How can I solve this problem?
The code I wrote to receive temporary table:
The table I get (CNT is always 1 – that’s correct:
The code I wrote to get my measure (the numbers obtained are correct):
Hours w/o visits =
8 - SUMX(
SUMMARIZE(
FILTER(
'GTM', ('GTM'[HOUR] >= 7 && 'GTM'[HOUR] <= 19)
),
'GTM'[CALENDAR_DAY], 'GTM'[SALES_ROUTE_PO1], 'GTM'[HOUR], "CNT",
DISTINCTCOUNT('GTM'[HOUR])),
[CNT]
)
The table I receive in Power BI (Only positive numbers should remain, and negative numbers/zero should be counted as zero. Accordingly, the result is not correct. Here I need IF function.