Totals for year showing wrong value

[MrMP]

Hi All,

I have a table below: (these are all measures)

1. First field is coming from balance sheet and as you can see has values for quarters and year is the amount in the last quarter.

2. Second field coming from PnL and has values for all quaters where year is summation of all quarters

3. Result column is Balance Sheet Field + PnL Field. As you can see for quarters it works fine, but I cant calculate the total. It has to be also 1.533.088.

Hope you can help community! 🙂

Thank you!

[Mikelytics]

Hi @MrMP 

In your initial request you highlighted the bottom right total to be wrong. But based on this feedback also the middle total must be wrong from the beginning because it seems to be a running total.

If that is correct, than PnL formula should need to be fixed upfront. But on the other side if column PnL is a running total then you already have the total in Row Number 4.

So from my understanding the easiest and most logical thing to do here is to turn of the total.

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[Mikelytics]

HI @MrMP ,

Can you please share the measures you use for the columns?

Best regards

Michael

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

[MrMP]

1. Balance Sheet Field - 

VAR lastquarter = MAX('Calendar'[Quarter Number])
VAR lastyear = MAX('Calendar'[Year])
RETURN
SWITCH(TRUE(),
ISINSCOPE('Calendar'[Quarter Number]), Sum(Balance Sheet Field),
ISINSCOPE('Calendar'[Year]),  CALCULATE(Sum(Balance Sheet Field)), FILTER('Calendar','Calendar'[Quarter Number]=lastquarter && 'Calendar'[Year]=lastyear)))

2. PnL Field - 

SWITCH(TRUE(),
ISINSCOPE('Calendar'[Quarter Number]),Sum(Revenue Field) ,
ISINSCOPE('Calendar'[Year]),SUMX(SUMMARIZE('Calendar','Calendar'[Year],'Calendar'[Quarter Number]),sum(Revenue Field) ), 
SUMX(SUMMARIZE('Calendar','Calendar'[Year],'Calendar'[Quarter Number]),sum(Revenue Field) ))

Lowest granularity of the calendar is Quarter.

Thank you!!!

[Mikelytics]

Hi @MrMP 

Can you please try to build a third measure like the following?

Result =
SUMX(
   VALUES('Calendar'[Quarter Number]),
   [PnL Field] + [Balance Sheet Field]
)

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[MrMP]

@Mikelytics wrong total again. Any other ideas?

[Mikelytics]

@MrMP 

ok, I think I know why, 

Please try the following

Result =

[Balance Sheet Field]

+

SUMX(
   VALUES('Calendar'[Quarter Number]),
   [PnL Field]
)

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[MrMP]

This total of 2.831.269 should be the same like Q4 of the year. So 1.533.088.