The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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!
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.
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. |
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
37 | |
36 | |
30 | |
29 | |
21 |
User | Count |
---|---|
61 | |
41 | |
32 | |
18 | |
16 |