Forum Discussion
If statement not totalling
Hello
I need some help with an IF Statement not totalling.
I am trying to calculate new customers and lost customers. I have a rolling 12 months calculation current year and a rolling 12 months calculation for the prior year.
My IF statement says
7 Replies
Hi Charli2516 - You can achieve this using the SUMX function
create below measure:
NEW CUSTOMER CY =
SUMX(
VALUES(TableName[CustomerID]), -- Replace 'TableName[CustomerID]' with the column that uniquely identifies your customers
IF(
AND([SALES LY 2023] = 0, [SALES CY 2024] > 0),
1,
0
)
)Hope the above works. please check.
i have put the new calculation in the 'Total New Customers' Column, and as you can see this doesnt pull through the same data as the 'New Customer CY'. It does total however..?
Hi Charli2516 - can you please modify and update the rows and the total, modify NEW CUSTOMER CY to use the same row-by-row logic applied in Total New Customers.
NEW CUSTOMER CY =
SUMX(
VALUES('YourTable'[Customer]),
IF(AND([SALES LY 2023] = 0, [Sales CY 2024] > 0), 1, 0)
)Add a table visual with detailed columns (e.g., Customer, [SALES LY 2023], [Sales CY 2024]).
Verify the row-level logic and ensure [SALES LY 2023] and [Sales CY 2024] return the expected values.
Hi Charli2516 ,
please try the bellow measure:
NEW CUSTOMER CY = SUMX( VALUES(CustomerTable[CustomerID]), IF( AND( [SALES LY 2023] = 0, [Sales CY 2024] > 0 ), 1, 0 ) )Hi Charli2516 ,
Have you solved your problem? If it is solved, please share your solution and accept it as solution or mark the helpful replies, it will be helpful for other members of the community who have similar problems as yours to solve it faster. Thank you very much for your kind cooperation!
Best Regards,
Zhu
