Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello -
This is probably a simple fix, but I am very new to DAX and cannot figure out the issue.
I have a Measure [Transaction Submitted] that I am using in charts that divide values into Departments. [Transaction Submitted] is also represented in a card as well that needs to be responsive to Department selection in the chart, which I have accomplished with the help of the community. (Many thanks to @v-cgao-msft ) Here is that DAX:
Transaction Submitted =
CALCULATE (
SUM ( 'Transaction List'[Budget] ),
FILTER (
ALLSELECTED ( 'Transaction List' ),
OR (
'Transaction List'[State] = "Ongoing"
'Transaction List'[State] = "Complete"
)
&& ( 'Transaction List'[Spent] = 0
&& 'Transaction List'[Processing] = 0
&& 'Transaction List'[Pre-Processing] = 0 )
)
)
I am also referencing the [Transaction Submitted] above in another Measure [Aggregate Spend] that just SUM's two Measures [Transaction Submitted] and [Completed Transactions] in the charts on the page. Ex:
Aggregate Spend = 'Transaction List'[Transaction Submitted] + 'Transaction List'[Completed Transactions]
The problem is: for each Department, the value of [Aggregate Spend] above includes [Transaction Submitted]'s whole value - not JUST the value for that Department. So, if the total value of [Transaction Submitted] for all Departments is $100k, on my charts where it is divided into Departments, EACH Department shows $100k when it should show only that Departments portion of [Transaction Submitted]
I know the problem is not the OTHER measure [Completed Transactions] because when I remove [Transaction Submitted] from [Aggregate Spend], the value is properly divided by Department in the charts.
I'm thinking I'm missing something in the original DAX that would allow the total to be divided by Department in the charts, likely because my initial inquiry did not include this context.
Thank you in advance for any support!
Solved! Go to Solution.
You may want to take it easy on the filter stuff. Here is an alternative option
Transaction Submitted =
CALCULATE (
SUM ( 'Transaction List'[Budget] ),
ALLSELECTED ( 'Transaction List' ),
'Transaction List'[State] IN { "Ongoing", "Complete"},
'Transaction List'[Spent] = 0,
'Transaction List'[Processing] = 0,
'Transaction List'[Pre-Processing] = 0
)
As for your question - Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
You may want to take it easy on the filter stuff. Here is an alternative option
Transaction Submitted =
CALCULATE (
SUM ( 'Transaction List'[Budget] ),
ALLSELECTED ( 'Transaction List' ),
'Transaction List'[State] IN { "Ongoing", "Complete"},
'Transaction List'[Spent] = 0,
'Transaction List'[Processing] = 0,
'Transaction List'[Pre-Processing] = 0
)
As for your question - Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |