How to get the average of two number cards that have average calculation

[malaw]

Hi Everyone,

I have two number cards that each has the average value for a specific column from different tables.

my goal is to get the average value for these two cards. 

currently, I'm trying to use a new quick measure with a DAX formula but I'm not getting the correct results as I'm sure my calculation is not correct.

This is the current formula 

Measure 2 = (SUM(LeadTimeNew[Lead Time by Release YAML (Days)]) + SUM(LeadTime[Lead Time by Release (Days)])) / 2

the first card is getting the average of this column 

Lead Time by Release (Days) = CALCULATE(MAX('LeadTime'[Lead Time by Commit]), ALLEXCEPT('LeadTime',LeadTime[Release ID]))

the second card is the average of this column 

Lead Time by Release YAML (Days) = CALCULATE(MAX('LeadTimeNew'[Lead Time]), ALLEXCEPT('LeadTimeNew', LeadTimeNew[Stage ID]))

 

Any help is greatly appreciated 

[harshnathani]

Hi @malaw ,

Try this measure

Measure =
VAR a =
    CALCULATE (
        SUM ( LeadTimeNew[Lead Time by Release YAML (Days)] ),
        ALL ( LeadTimeNew )
    )
VAR cra =
    COUNTROWS ( LeadTimeNew )
VAR b =
    CALCULATE (
        SUM ( LeadTime[Lead Time by Release (Days)] ),
        ALL ( LeadTime )
    )
VAR crb =
    COUNTROWS ( LeadTime )
VAR d = a + b
VAR e = cra + crb
RETURN
    DIVIDE (
        d,
        e
    )

or

Measure =
VAR a =
    CALCULATE (
        SUM ( LeadTimeNew[Lead Time by Release YAML (Days)] ),
        ALL ( LeadTimeNew )
    )
VAR b =
    CALCULATE (
        SUM ( LeadTime[Lead Time by Release (Days)] ),
        ALL ( LeadTime )
    )
VAR d = a + b
RETURN
    DIVIDE (
        d,
        2
    )

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[malaw]

Hi @harshnathani 

Thanks for your prompt response 🙂 

These are the two average values I have for Lead Time by Release (Days) = 18 

and Lead Time by Release YAML (Days) = 5.59

there are some filters applied for each card to select a specific project to calculate the lead time for each project (would this affect the calculation that we need to do?) 

The goal is to get a combined average value for both

when I used the first calculation you provided I got this value: 6.06 K

and when I used the second calculation I got this value: 35.50M

The formulas make sense but I'm still not getting the correct results 😞 

Please let me know your thoughts

[harshnathani]

Hi @malaw ,

Since you have filters applied, it is affecting the averages.

Try removing ALL() from the formula and see.

Als can you share a screen shot of your cards, filters and measures.

Regards,

Harsh Nathani

[malaw · Accepted solution]

Hi @harshnathani

Removing the All() worked, Thank you so much for your help 🙂

This is the final code

Measure = 
VAR a =
    CALCULATE (
        SUM ( LeadTimeNew[Lead Time by Release YAML (Days)] )
    )
VAR cra =
    COUNTROWS ( LeadTimeNew )
VAR b =
    CALCULATE (
        SUM ( LeadTime[Lead Time by Release (Days)] )
    )
VAR crb =
    COUNTROWS ( LeadTime )
VAR d = a + b
VAR e = cra + crb
RETURN
    DIVIDE (
        d,
        e
    )