Forum Discussion

WBscooby's avatar
WBscooby
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 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])

 

 

Need Help

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    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.

     

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    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's avatar
      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!
       
       
  • WBscooby's avatar
    WBscooby
    Helper III
    4 years ago

    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!