Looking to Sum Values returned from a lookup measure

[jignaski18]

I cannot seem to get the appropriate DAX to get the sum of lookup values in a matrix. The measure is looking up the expectation by the last date. The values pull in correctly but i do not know how to get them to sum in the subtotal. 

VAR _E = 
CALCULATE(
    MAX('DM Stage Expectations'[Expectationdate]),
        FILTER(ALL('DM Stage Expectations'),
        ('DM Stage Expectations'[Expectationdate]<=max('DM Stage Expectations'[Expectationdate])&&
        'DM Stage Expectations'[Expectationdate]<=enddate&&
        'DM Stage Expectations'[Avg Expectation]<>0&&
        'DM Stage Expectations'[Customer]=cust
        &&'DM Stage Expectations'[Fleet]=crew)))
VAR ZIP = LOOKUPVALUE('DM Stage Expectations'[ZipperExpectation],'DM Stage Expectations'[Expectationdate],_E)
VAR STK = LOOKUPVALUE('DM Stage Expectations'[StackExpectation],'DM Stage Expectations'[Expectationdate],_E)
VAR EXPT = IF(CALCULATE(COUNTA('Eng_Metrics_RPT'[Zipper]),'Eng_Metrics_RPT'[Zipper] IN { TRUE })>0,ZIP,STK)
RETURN
 SWITCH(SELECTEDVALUE(Categories[ID]),
    8,EXPT
)

[v-stephen-msft]

Hi @jignaski18 ,

Please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Generally speaking, HASONEVALUE function is often used to solve the subtotal problem. Please kindly refer to: 

https://community.powerbi.com/t5/Desktop/Subtotal-not-working-properly/m-p/644407#M308803

Best Regards,

Stephen Tao

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

[jignaski18]

Thank you Stephen. I cannot share the file due to the nature of the connections, but here is a screenshot of what i am trying to accomplish in the subtotal. The attached excel file has a table of how the data is stored (actually stored in a sharepoint list). Example data

Id like to mention that the values to the left of the "/" are a calculated value and the values to the right are a lookup from the sharepoint list. 

[v-stephen-msft]

Hi @jignaski18 ,

I can't download your Eample data.  Please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

How did the data in the red box come from? Is it in text format?

For now, it is suggested that you try to create the following measure to display values.

IF(HASONEVALUE('Table'[District]),A,B)

In A, put the field in the red box in your picture, and in B, write the measure of how you add the above values.

Best Regards,

Stephen Tao

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