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

 

 

Need Help

7 Replies

  • 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

         

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

    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