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.
I am trying to compare actual invoices to budgeted amounts. I have the invoice data available at a very transactional level whereas we budget at a more aggregate level. I would like to know if there is a way to still be able to see the invoice amounts contributing to the "amount" column but not have the budgeted amount repeat for every line of the invoice? You can see that 208.33 repeats everytime there is a professional service invoice. Preferably it would be blank except for the subtotal.
Solved! Go to Solution.
What is the table and field name where the item '2694960' is sitting? Basically, what it the field name that holds the lowest level of detail you are showing in your visual? I am going to make it up and call it 'Transaction Table'[Invoice Header].
In your budget measure you can use ISINSCOPE to check if you are at too low a level and return blank.
If your original budget measure is like this.
Budget = SUM ( 'Budget Table'[Budget Amount] )
Your new measure would be like this.
Budget =
IF (
ISINSCOPE ( 'Transaction Table'[Invoice Header] ),
BLANK (),
SUM ( 'Budget Table'[Budget Amount] )
)
What is the table and field name where the item '2694960' is sitting? Basically, what it the field name that holds the lowest level of detail you are showing in your visual? I am going to make it up and call it 'Transaction Table'[Invoice Header].
In your budget measure you can use ISINSCOPE to check if you are at too low a level and return blank.
If your original budget measure is like this.
Budget = SUM ( 'Budget Table'[Budget Amount] )
Your new measure would be like this.
Budget =
IF (
ISINSCOPE ( 'Transaction Table'[Invoice Header] ),
BLANK (),
SUM ( 'Budget Table'[Budget Amount] )
)
Its been a few months but I wanted to know if you could help me take this further:
I wrote this measure with your help:
I like that the invoice detail is blank for the parts of the budget that do not have this detail the problem is that the YTD budget is aggregating on repeat for each account line ( see 50001 Payroll). Idealy the total budget YTD would populate for 50001 payroll only and so on for each account and 493,604.01 would only be the total at the bottom. Both budget and Actuals share the account detail, only budget does not go further down into invoice and vendor name. Let me know if there is a way to modify the dax to include this! Thanks in advance.
Thank you! That worked perfectly. The budget amount was a field from a query so I just created a measure to capture the If(ISINSCOPE()) Function.
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 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |