Help with SUMX (VALUES...

[WBscooby]

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 the number of resales by outcome ID which sits in the outcome tables . I then use a sumx to total the resales by outcome ID. 

In the outcome table, I have a field 'Outcome Tier'. When I produce a chart of total sales including outcome tier from the outcome table, the data is incorrect. If I add this column to the resales table, sumx will work correctly. I don't really want to do this as it doesn't seem logical to add columns to make the measure work.

Is anyone able to identify where my measures are incorrect - Id like to try to understand what exactly I'm asking the measure to do so I know where I'm going wrong for future work! 

Thank you

Measure 1:

#Sales_Resale =
Var _id = MAX (Sales[Id])
VAR _ResaleType = min(SummarizeResale[ResaleType])
VAR _OutcomeType = min(Slicer_CohortOutcomeGroup[Outcome Group])
Var _date =
IF(_OutcomeType="Main",
CALCULATE(MIN(Outcomes[DateOutcomeUSE]),
ALLSELECTED('Outcomes'),
Outcomes[Case Id]=_id,
Outcomes[OutcomeType2]="Main"),
IF(_OutcomeType="Sub",
CALCULATE(MIN(Outcomes[DateOutcomeUSE]),
ALLSELECTED('Outcomes'),
Outcomes[Case Id]=_id,
Outcomes[OutcomeType2]="Sub"),
CALCULATE(MIN(Outcomes[DateOutcomeUSE]),
ALLSELECTED('Outcomes'),
Outcomes[Id]=_id)
))

Var _OutcomeID =
Calculate(Min(Outcomes[OutcomeID]),
ALLSELECTED(Outcomes),
Outcomes[Id]=_id,
Outcomes[DateOutcomeUSE]= _date
)

Var CountResales =
if(_ResaleType="ALL",
CALCULATE(COUNT(Resales[Resale_ID]),
VALUES(Outcomes[OutcomeID]),Resale[OutcomeID]=_OutcomeID,
Resales[Resale]=1),
CALCULATE(COUNT(Resales[Resale_ID]),
VALUES(Outcomes[OutcomeID]),Resales[OutcomeID]=_OutcomeID,
Resales[Resale]=1,
Resales[Resale_Type]="Main")
)
Return
Countresales

Measure 2:

#TotalResales = sumx(VALUES(Outcomes),[#Sales_Resale])

[WBscooby]

Hi Miguel

Thanks for your reply. I did try to create some dummy data from my file but struggled because even the fields have sensitive names so it was messing up all of my measures!
I can see that when it comes to my SUMX measure it is summing for all outcome IDS, not just those specified in the variables in the first measure. I did experiment with adding a field to the SUMX measure but this then stops the totals in my matrix working correctly.

What I really need to understand is how to only sum the values in the outcomes table for the variables defined in the initial measure.

Any advice would be great - thank you!

[v-zhangti]

Hi, @WBscooby 

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]

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!

[MFelix]

Hi @WBscooby ,

Without any data is difficult to pin point your error however believe the question is related with the way you are making the SUMX that is based on the full table Outcomes, I assume that table have several columns so when you get the values for the table you are picking up all the table. Try to do only the specific column you need, not sure if is the tier.