DAX Virtual tables & total row evaluation

pmcmonag — Mon, 24 May 2021 17:13:06 GMT

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.

Re: DAX Virtual tables & total row evaluation

AlB — Mon, 24 May 2021 19:08:31 GMT

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.

topic DAX Virtual tables & total row evaluation in DAX Commands and Tips

DAX Virtual tables & total row evaluation

Re: DAX Virtual tables & total row evaluation