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

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