Forum Discussion

pmcmonag's avatar
pmcmonag
Icon for Advocate IV rankAdvocate IV
5 years ago

DAX Virtual tables & total row evaluation

Is it possible to create a virtual table which returns a different measure for each row based on some logic……….and then expect SUMX to produced the desired result in the total row?   Attached is a ...
DAX
evaluation context
Totals
Virtual Table
AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

Hi pmcmonag 

You need to trigger context transition for it to work propertly (so that the COUNTROWS takes into account what you have in the row of the visual)  . Just add a CALCULATE:

Total Income (Dynamic) V2 =
VAR TempTable =
    ADDCOLUMNS (
        VALUES ( Supporters[SupporterID] ),
        "Income",
            IF (
                CALCULATE ( COUNTROWS ( 'Corporate Finance Codes' ) ) > 0,
                [Total Income (by Corporate)],
                [Total Income]
            )
    )
RETURN
    SUMX ( TempTable, [Income] )

 

OR alternatively, a version a bit more compact:

Total Income (Dynamic) V3 =
SUMX (
    DISTINCT ( Supporters[SupporterID] ),
    IF (
        CALCULATE ( COUNTROWS ( 'Corporate Finance Codes' ) ) > 0,
        [Total Income (by Corporate)],
        [Total Income]
    )
)

 

 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.