How do I calculate the average of another measure and display in a Stacked Bar Chart?

[rmattern]

I have a measure that calculates Earned Value per a Submittal number (upper chart).  I also want an average Earned Value calcualted for all the Submittal numbers,  I can't seem to get this to work.  The upper Stacked Chart shows the individual EV.  The lower chart should be a simple calculation of the sum of the upper values / the count of the upper items.  Which in this case should result in (82+81+97+84+38+84+45)/7 or 511/7 = 73%.  Any help would be appreciated.

[v-jiascu-msft]

Hi @rmattern,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[rmattern]

Earned Value for top chart.  We have confirmed the top chart values are correct with hand calculations.

Earned Value = IF (Sum(CyientDataOnlyLastSubmittal[OTD]) >= 0,
      If(Sum(CyientDataOnlySubmittal1[FTA]) >= 0,
       If(CyientData[Estimated / Actual] > 0,
        If( CyientData[ROPQ] >= 0 ,(0.15 * Sum(CyientDataOnlyLastSubmittal[OTD])) + (0.15 * CyientDataOnlySubmittal1[FTA Avg]) + (0.2 * CyientData[Estimated / Actual]) + (0.5 * (1- CyientData[ROPQ])),0),0),0),0)

The bottom chart just uses the slicer on the left side for SOW.  I do not have a calculation (can't figure one out) for the bottom  chart to be correct.

[TomMartens]

Hey, I'm wondering if it will work if you just put an AVERAGEX(...) around your existing measure like so:

AVERAGEX(
VALUES(reference to thecolumn that is used on the xaxis)
,your measure
)

Regards, Tom

[Anonymous]

@rmattern can you please provide sample data as I think there is a better way to do both the calculations. If possible please create a sample power bi file with dummy data, upload it to dropbox or google drive and post the link here.

If you want to try something calculate the values per submittal number using allexcept and then use those values to calculate the average values.

https://msdn.microsoft.com/en-us/query-bi/dax/allexcept-function-dax

[rmattern]

If I understand you correctly I should try the following:

 IF (Sum(CyientDataOnlyLastSubmittal[OTD]) >= 0,
      If(Sum(CyientDataOnlySubmittal1[FTA]) >= 0,
       If(CyientData[Estimated / Actual] > 0,
        If( CyientData[ROPQ] >= 0 ,

                          CALCULATE( (0.15 * Sum(CyientDataOnlyLastSubmittal[OTD])) + (0.15 * CyientDataOnlySubmittal1[FTA Avg]) + (0.2 * CyientData[Estimated / Actual]) + (0.5 * (1- CyientData[ROPQ])) , ALLEXCEPT(CyientDataOnlyLastSubmittal[SubmittalNumber]) )   ,0),0),0),0)

[Anonymous]

@rmattern Can you please provide more details?

1) Sample of original data

2) Formulas you have used to calculate the avg for the 2 cases