Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Visual total correct but rows dont add up

Hi

 

In my visual below I am showing the Balance as at Feb20 my first column and the movement amounts in the next 3 columns.

 

The grand total for the movement columns are correct, and the sum of the rows for these columns add up to the correct grand total.

 

However, for the balance column, the rows dont add up to the grand total which is the correct amount i.e. 10.2m is the correct total, but the sum of the rows for the balance column is like 2m. It is like there are rows missing..

 

Please can you advise? See detail below.

 

 

Here are my Measures: 

 

FAC Claims Debtors move:=CALCULATE(SUM('RICLAIMSDEBTORS'[AmountInclVAT]),RICLAIMSDEBTORS[RI Type Group]="FAC")

FAC Claims Debtors close ALL:=
VAR CumulativeTotal = CALCULATE([FAC Claims Debtors move],
FILTER(ALL('SSAS V_DIM_AccountingPeriod - ReportingPeriod'),'SSAS V_DIM_AccountingPeriod - ReportingPeriod'[ReportingDateEnd]<=MAX('SSAS V_DIM_AccountingPeriod - ReportingPeriod'[ReportingDateEnd]))
)
RETURN
IF(ISBLANK([FAC Claims Debtors move]),BLANK(),CumulativeTotal)

5 Replies

  • Anonymous , where is balance formula ?

     

    Try this like


    FAC Claims Debtors close ALL:=

     CALCULATE([FAC Claims Debtors move],
    FILTER(ALL('SSAS V_DIM_AccountingPeriod - ReportingPeriod'),'SSAS V_DIM_AccountingPeriod - ReportingPeriod'[ReportingDateEnd]<=MAX('SSAS V_DIM_AccountingPeriod - ReportingPeriod'[ReportingDateEnd]))
    )

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi amitchandak

       

      See below:

       

      Looks like ours is the same..

       

      • amitchandak's avatar
        amitchandak
        Super User

        Anonymous , I want isblank to removed. that will can lead to grand total issue. But I am not sure in this case 

         

        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Anonymous's avatar
    Anonymous
    Not applicable

    amitchandak 

     

    It seems to be related to my other issue below where there should only be rows for "FAC"..

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    HI Anonymous,

    Can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

    How to Get Your Question Answered Quickly  

    In addition, you can also take a look at the following blog to know more about measure total level calculations:

    Measure Totals, The Final Word  
    Regards,

    Xiaoxin Sheng