Forum Discussion
calculations on columns in an array
Good night
I have created a matrix to total my company's personnel budget and actual spending. In the rows I have grouped by years and in the columns I have grouped by whether it is budget or if it is spending. in the dataset I have identified in a field called item if it is spending or is budget. I would need to be able to show another column next to it with the difference between budget and expenditure and with the percentage of expenditure, being in a matrix I do not know how to get this done. You could give me an idea of how to create these calculations.
Hi, tronamen
According to your requirement, you can try these measures:
Sum of Budget = CALCULATE(SUM('Budget and spending'[Number]),FILTER('Budget and spending',[Item]="Budget"))Sum of Spending = CALCULATE(SUM('Budget and spending'[Number]),FILTER('Budget and spending',[Item]="Spending"))Difference between budget and expenditure = [Sum of Budget]-[Sum of Spending]Percentage of expenditure = DIVIDE([Sum of Spending],[Sum of Budget])And you can get what you want, like this:
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
tronamen , In this case, you might have to create measure like
budget = calculate([measure], table[item] ="budget")
expenditure= calculate([measure], table[item] ="expenditure")
diff =[budget] -[expenditure]
- v-robertq-msft
Community Support
Hi, tronamen
According to your requirement, you can try these measures:
Sum of Budget = CALCULATE(SUM('Budget and spending'[Number]),FILTER('Budget and spending',[Item]="Budget"))Sum of Spending = CALCULATE(SUM('Budget and spending'[Number]),FILTER('Budget and spending',[Item]="Spending"))Difference between budget and expenditure = [Sum of Budget]-[Sum of Spending]Percentage of expenditure = DIVIDE([Sum of Spending],[Sum of Budget])And you can get what you want, like this:
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
