CODEWITHSUNDEEP
excelpivot-tablepowerpivot
user17360738
user17360738

Reputation: 3

Excel Pivot Data model measures sum

Hello I have in Excel 365 the following data model (Access bindet in the excel data model).

Date Customer Currency ...
2023/02/01 Customer1 10,53
2023/02/05 Customer3 -125,38
2023/03/11 Customer2 -501,23
2023/01/25 Customer1 1,25
2023/01/10 Customer2 354,21
... ...

There are more columns but I think so discuss my problem it's enough.

Now I want a pivot table and a pivot diagram with the following structure.

Month Incoming payment Outgoing payment Balance
Jan 355,46 0 355,46
Feb 10,53 125,38 -114,85
Mar 0 501,23 -501,23

As you see I want the outgoing positiv too! And important because of the data model I cant use calculated field or element in the pivot table directly.

I will filter the pivot with slice, f.e. year, customer,...

I tried too use measures f.e. sum but it doen't fit. I know I can show a result column for the pivot table but this doesn't help for the diagram.

I found this questions but it doesn't help me: https://stackoverflow.com/questions/73394124/custom-column-in-a-pivot-table-from-the-data-model-in-excel

Upvotes: 0

Views: 257

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9052

Just create 3 simple measures:

Balance :=
0 + SUM( Table1[Currency] )

Incoming Payment :=
CALCULATE(
    [Balance],
    Table1[Currency] >= 0
)

Outgoing Payment :=
- CALCULATE(
    [Balance],
    Table1[Currency] < 0
)

Upvotes: 0

Related Questions