Forum Discussion

Icon for Helper III rank

Helper III

4 years ago

Help with SUMX (VALUES...

Hi I'm hoping for some help with my measures. My data is quite sensitive so it's not easy to share. I've attempted to rewrite as sales so hope it makes sense I have written a measure to count...

Icon for Community Support rank

Community Support

4 years ago

Can you simulate some of the data and provide it to us for testing? Sensitive data can be replaced by simple characters. And tell us what you expect the output to look like.

Best Regards,

Community Support Team _Charlotte

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

WBscooby
Helper III
4 years ago
Hi V-zhangti

Thank you for replying. My data is really difficult to simulate as even the field names are sensitive. I have attempted to mock up a pbix file to demonstrate my problem.
https://1drv.ms/u/s!Ap6q8W-mvm27g-dWehgkV6-p33VVsA?e=bGj3nV

Please ignore the earlier measures. The measures I have created in the file are :

CountResales =

Var _Cust = Max (FACTSales[CustomerID])
Var _Date = CALCULATE(min(FACTSales[DateOrigSale]),ALLSELECTED(FACTSales),FACTSales[CustomerID]=_Cust)
Var _ID = CALCULATE(min(FACTSales[CohortID]),ALLSELECTED(FACTSales),FACTSales[CustomerID]=_Cust,FACTSales[DateOrigSale]=_Date)

Var _CountResales =

CALCULATE(DISTINCTCOUNT('DIMReSales transactions'[TransactionID]),
VALUES('DIMReSales transactions'[TransactionID]),'DIMReSales transactions'[CohortID]=_ID,
'DIMReSales transactions'[New Sale]=1)

Return

_CountResales

And

SumResales = Sumx(values('FACTSales'),[CountResales])

The measure works well in the matrix but when I add it to the stacked column and include Tier the calculation is wrong. I think it is because the sumx is losing the filter created in the first measure but I don't really understand how to rectify this. I've been trying for days! Any help would be very much appreciated - thank you!