DIVIDE different number denominators

[dan_martin]

Hi all,

I need to return the accurate values,  my table is build with 4 different measures. I need to calculate an average, but, as you see for 3 columns I have no value in the first column, so the denominator should be 2 and the correct value 100%.

My dax is a basic formula:  Average = (value1+value2+value3) / 3. 

[Jihwan_Kim]

Hi,

I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

Average expected percent: = 
VAR _onepercenttable =
    ADDCOLUMNS (
        VALUES ( Data[Name] ),
        "@one percent", [Trial one percent measure:]
    )
VAR _twopercenttable =
    ADDCOLUMNS (
        VALUES ( Data[Name] ),
        "@two percent", [Trial two percent measure:]
    )
VAR _threepercenttable =
    ADDCOLUMNS (
        VALUES ( Data[Name] ),
        "@three percent", [Trial three percent measure:]
    )
VAR _uniontables =
    SELECTCOLUMNS (
        UNION ( _onepercenttable, _twopercenttable, _threepercenttable ),
        "@Name", Data[Name],
        "@percent", [@one percent]
    )
RETURN
    AVERAGEX ( _uniontables, [@percent] )

[dan_martin · Accepted solution]

Thank you Jihwan Kim, it worked!