cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Totals for year showing wrong value

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!

7 REPLIES 7
Resident Rockstar

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!

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Resident Rockstar

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!

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Helper III

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!!!

Resident Rockstar

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!

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Helper III

@Mikelytics wrong total again. Any other ideas?

Resident Rockstar

ok, I think I know why,

``````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!

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Helper III

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors
Top Kudoed Authors