Forum Discussion

MrMP's avatar
MrMP
Icon for Helper III rankHelper III
3 years ago

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. Seco...
Need Help
MrMP's avatar
MrMP
Icon for Helper III rankHelper III
3 years ago

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's avatar
Mikelytics
Icon for Resident Rockstar rankResident Rockstar
3 years ago

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's avatar
    MrMP
    Icon for Helper III rankHelper III
    3 years ago

    Mikelytics wrong total again. Any other ideas?

     

     

    • Mikelytics's avatar
      Mikelytics
      Icon for Resident Rockstar rankResident Rockstar
      3 years ago

      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's avatar
        MrMP
        Icon for Helper III rankHelper III
        3 years ago

         

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