table per section Actual with total and then Ad-hoc and Budgeted with totals as well as sum

[CJKPowerBI]

hello all,

i have my data in excel like below

Actual / Budgeted / Ad-hoc	Transcation Date	Amount	Who	Description
Actual	9/23/2018	$1,000.00	CJK	Item 1
Actual	9/26/2018	$200.00	CJK	Item 2
Budgeted	9/26/2018	$130.00	CJK	Item 3
Budgeted	9/26/2018	$26.00	CJK	Item 4
Ad-hoc	9/26/2018	$130.00	CJK	Item 5
Ad-hoc	9/26/2018	$26.00	CJK	Item 6

and i would like a formula to give me the below results...

and Grand Total for 26 : ($200+(-$312) = -$112

let me know pls.

[v-yanjiang-msft · Accepted solution]

Hi @CJKPowerBI ,

According to your description, I create a sample.

Here's my solution. Create two calculated columns.

Actual/Ad-hoc Budgeted =
IF ( [Actual / Budgeted / Ad-hoc] = "Actual", "Actual", "Ad-hoc Budgeted" )

Column =
IF (
    'Table'[Actual/Ad-hoc Budgeted] = "Actual",
    'Table'[Amount],
    'Table'[Amount] * -1
)

Then put the columns in a matrix like below, get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CJKPowerBI]

thank you that worked great

[Shaurya]

Hi @CJKPowerBI,

Create a Type column that treats Ad-hoc and Budgeted as the same field and Actual as another. Then use the following formula to get the total:

Total = IF(MAX('Table'[Type])="Ad-hoc Budgeted", CALCULATE(SUM('Table'[Amount]))*-1, CALCULATE(SUM('Table'[Amount])))

Result:

As far as the grand total is concerned, you can refer to this post:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Works for you? Mark this post as a solution if it does!

[CJKPowerBI]

hello @Shaurya  and thank you for your reply,

couple of questions:

1) i have a column named "Actual / Budgeted/ Ad-hoc" as shown at my initial request but i dont understand what you mean by "Create a Type column that treats Ad-hoc and Budgeted as the same field and Actual as another"? can you pls explain a bit more.

2) the formula is a measure or a column

3) the table which you show is a "slicer" a "table" or a "matrix"?

let me know pls.