DAX Virtual tables & total row evaluation

[pmcmonag]

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 dummy .pbix of the scenario required in full (mimicking coding and source systems from real data). Presenting it as a DAX problem, but could well be a data modelling answer!

Dummy pbix 

Summary:

Require a solution to view Income in the view of a Corporate partner to a Charity, where applicable, if not just view it as an individual supporter. Scenario is that Microsoft could give a donation from Head Office, their UK office or an employee could run a race for them. So the standard view would be income per those three Supporter records. However also want to see the view of Microsoft as a whole entity.

This seems to be OK, but then add another individual who is not coded to raised for Microsoft and the current measure doesn’t evaluate correctly.

[AlB]

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.