Forum Discussion

DionTN's avatar
DionTN
Icon for Helper II rankHelper II
4 years ago
Solved

Add quick calculations as new column

The upper table is the result i want. So for "lichaamssamenstelling" I want a result of 7.63 in the left column second table. My calculation works as you can see in the third column. Why is this not working in the folowing function?

 

Kolom =
IF (
    webgraph[Category] = "Lichaamssamenstelling",
    AVERAGEX (
        KEEPFILTERS ( VALUES ( 'calctable'[LastValue] ) ),
        CALCULATE ( AVERAGE ( 'calctable'[BodyComp2] ) )
    ),
    IF (
        webgraph[Category] = "cardio",
        AVERAGEX (
            KEEPFILTERS ( VALUES ( 'calctable'[LastValue] ) ),
            CALCULATE ( AVERAGE ( 'calctable'[cardio2] ) )
        ),
        IF (
            webgraph[Category] = "Microcirculatie",
            AVERAGEX (
                KEEPFILTERS ( VALUES ( 'calctable'[LastValue] ) ),
                CALCULATE ( AVERAGE ( 'calctable'[Micro2] ) )
            ),
            IF (
                webgraph[Category] = "stress",
                AVERAGEX (
                    KEEPFILTERS ( VALUES ( 'calctable'[LastValue] ) ),
                    CALCULATE ( AVERAGE ( 'calctable'[stress2] ) )
                ),
                IF (
                    webgraph[Category] = "Vertebraal",
                    AVERAGEX (
                        KEEPFILTERS ( VALUES ( 'calctable'[LastValue] ) ),
                        CALCULATE ( AVERAGE ( 'calctable'[Vert2] ) )
                    ),
                    0
                )
            )
        )
    )
)

In my opinion the calculation is identical to the quick calculation in the 3th column:

Gemiddelde van BodyComp2 per LastValue = 
AVERAGEX(
	KEEPFILTERS(VALUES('calctable'[LastValue])),
	CALCULATE(AVERAGE('calctable'[BodyComp2]))
)
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi DionTN 

     

    Do you mean "Quick measure" by "quick calculation"? Your [Kolom] is a calculated column rather than a measure, so the measure DAX will work differently when it's applied to a calculated column. You can try below code to create a measure and add it to the table visual to check the result.

    New Measure =
IF (
    SELECTEDVALUE ( webgraph[Category] ) = "Lichaamssamenstelling",
    AVERAGEX (
        KEEPFILTERS ( VALUES ( 'calctable'[LastValue] ) ),
        CALCULATE ( AVERAGE ( 'calctable'[BodyComp2] ) )
    ),
    IF (
        SELECTEDVALUE ( webgraph[Category] ) = "cardio",
        AVERAGEX (
            KEEPFILTERS ( VALUES ( 'calctable'[LastValue] ) ),
            CALCULATE ( AVERAGE ( 'calctable'[cardio2] ) )
        ),
        IF (
            SELECTEDVALUE ( webgraph[Category] ) = "Microcirculatie",
            AVERAGEX (
                KEEPFILTERS ( VALUES ( 'calctable'[LastValue] ) ),
                CALCULATE ( AVERAGE ( 'calctable'[Micro2] ) )
            ),
            IF (
                SELECTEDVALUE ( webgraph[Category] ) = "stress",
                AVERAGEX (
                    KEEPFILTERS ( VALUES ( 'calctable'[LastValue] ) ),
                    CALCULATE ( AVERAGE ( 'calctable'[stress2] ) )
                ),
                IF (
                    SELECTEDVALUE ( webgraph[Category] ) = "Vertebraal",
                    AVERAGEX (
                        KEEPFILTERS ( VALUES ( 'calctable'[LastValue] ) ),
                        CALCULATE ( AVERAGE ( 'calctable'[Vert2] ) )
                    ),
                    0
                )
            )
        )
    )
)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    DionTN , Try a measure like

     

    Gemiddelde van BodyComp2 per LastValue =
    CALCULATE(AVERAGEX(VALUES('calctable'[LastValue]),
    CALCULATE(AVERAGE('calctable'[BodyComp2]))
    ), allselected())

    • DionTN's avatar
      DionTN
      Icon for Helper II rankHelper II
      4 years ago

      amitchandak 

      I need to change the Kolom calculation cause that result is not correct where i expect 7.63.

       

      Now I tried your example but it still gives 7.31:

      Kolom =
IF (
    webgraph[Category] = "Lichaamssamenstelling",
    CALCULATE (
        AVERAGEX (
            VALUES ( 'calctable'[LastValue] ),
            CALCULATE ( AVERAGE ( 'calctable'[BodyComp2] ) )
        ),
        ALLSELECTED ()
    ),
    0
)

       

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi DionTN 

     

    Do you mean "Quick measure" by "quick calculation"? Your [Kolom] is a calculated column rather than a measure, so the measure DAX will work differently when it's applied to a calculated column. You can try below code to create a measure and add it to the table visual to check the result.

    New Measure =
IF (
    SELECTEDVALUE ( webgraph[Category] ) = "Lichaamssamenstelling",
    AVERAGEX (
        KEEPFILTERS ( VALUES ( 'calctable'[LastValue] ) ),
        CALCULATE ( AVERAGE ( 'calctable'[BodyComp2] ) )
    ),
    IF (
        SELECTEDVALUE ( webgraph[Category] ) = "cardio",
        AVERAGEX (
            KEEPFILTERS ( VALUES ( 'calctable'[LastValue] ) ),
            CALCULATE ( AVERAGE ( 'calctable'[cardio2] ) )
        ),
        IF (
            SELECTEDVALUE ( webgraph[Category] ) = "Microcirculatie",
            AVERAGEX (
                KEEPFILTERS ( VALUES ( 'calctable'[LastValue] ) ),
                CALCULATE ( AVERAGE ( 'calctable'[Micro2] ) )
            ),
            IF (
                SELECTEDVALUE ( webgraph[Category] ) = "stress",
                AVERAGEX (
                    KEEPFILTERS ( VALUES ( 'calctable'[LastValue] ) ),
                    CALCULATE ( AVERAGE ( 'calctable'[stress2] ) )
                ),
                IF (
                    SELECTEDVALUE ( webgraph[Category] ) = "Vertebraal",
                    AVERAGEX (
                        KEEPFILTERS ( VALUES ( 'calctable'[LastValue] ) ),
                        CALCULATE ( AVERAGE ( 'calctable'[Vert2] ) )
                    ),
                    0
                )
            )
        )
    )
)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.