COLUMN TOTAL DOES NOT ADD UP

[Syndicate_Admin]

Hello community,
I have this case I hope someone can help me.

First we have the sales by customer :

B1 - Sellout (S) = SUM('TB - SALE'[VENTASINIGV])

Then I have to validate if this client did not buy me 6 months before then I consider it as a new client:

I1 - Sellout -6M < =

-- 01/03/2022 Min , 31/08/2022 Max , 30/09/2022 Actual

if(isblank([B1 - Sellout (S)])||[B1 - Sellout (S)]<=0,blank(),

if(isblank(CALCULATE([B1 - Sellout (S)],

DATESBETWEEN('TC - CALENDAR'[date],

DATE(YEAR(EOMONTH(Max('TB - SALE'[DATE]),-[Value - Periods])),MONTH(EOMONTH(Max('TB - SALE'[DATE]),-[Value - Periods])),1),

EOMONTH(Max('TB - SALE'[DATE]),-1)

))

)

||

CALCULATE([B1 - Sellout (S)],

DATESBETWEEN('TC - CALENDAR'[date],

DATE(YEAR(EOMONTH(Max('TB - SALE'[DATE]),-[Value - Periods])),MONTH(EOMONTH(Max('TB - SALE'[DATE]),-[Value - Periods])),1),

EOMONTH(Max('TB - SALE'[DATE]),-1)

))<=0

,1,

blank()

)

)

So far everything ok is validating correctly, now I need the totals to add up.

I2 - New Clients -6M< =

CALCULATE(countx(distinct('TB - SALE'[CustomerConnection]),if([I1 - Sellout -6M <]=1,1,blank())))

This measure, although it is adding correctly by column, the total of rows ignores it.
Try the following:

I3 - New Clients -6M< =

IF(COUNTROWS(VALUES('TC - CALENDAR'[Period])) =1,[I2 - New Customers -6M<],SUMX(VALUES('TB - SALE'[CustomerConnection]),1))

Which places me the 1 per row but in the end is adding 9 when in reality it should be 8.

Row labels	2022-2	2022-3	2022-4	2022-5	2022-6	2022-7	2022-8	2022-9	Total general
AREQUIPA.01.80123000000000126			1055						1055
AREQUIPA.01.80123000000000181		216		375		859			1450
AREQUIPA.01.80123000000000345						1354			1354
AREQUIPA.01.80123000000000411					10157				10157
AREQUIPA.01.80123000000001063								103	103
AREQUIPA.01.80123000000001718							648		648
AREQUIPA.01.80123000000010066				187		484			671
AREQUIPA.01.80123000000010406		352	0						352
AREQUIPA.01.80123000000010519								25	25
Total general		568	1055	562	10157	2697	648	128	15815

[v-xiaotang]

Hi @Syndicate_Admin 

Thanks for reaching out to us.

>>

The total row is to count the rows on it. There are 9 rows of 1 on it, so total=9. If you need it to output other values, you can use some functions, such as hasonevalue(), hasonefilter() and so on.

Something like this

IF(HASONEFILTER(table[col]), [measure], [total measure])

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.