Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

calculating expect top 10

hi,

 

I want to add the total balance which are not in top 10 to the bottom of the table .

 

I wrote the below formula (with the help of another answer by Cherie Chen)

but i cannot add it to the Rows. Can you please help ?

 

 

Measure = 
VAR a =
    SUMMARIZE (
        'Mizan Özet'
        ,'Mizan Özet'[Firma Adı]
        ,'Mizan Özet'[Hesap No]
        ,"Toplam", calculate (sum('Mizan Özet'[Toplam]), 'Mizan Full'[Hesap Grubu] = "320", 'Mizan Özet'[Toplam] < 0 )
     
    )
VAR b =
    ADDCOLUMNS ( a, "rank", RANKX ( a, [Toplam] ) )
RETURN
    CALCULATE (
        SUM ( 'Mizan Özet'[Toplam] ) ,
        FILTER ( b, [rank] > 10 )
    )

 

 

Need Help

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    Sorry I'm not clear what you mean "cannot add it to the Rows". Does the formula get the correct result or not?

    Would you please explain more.

    Best Regards,
    Community Support Team _ kalyj

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      hi there,

       

      thanks for your time. I could create totally another table including the top ten rows and 11th row as a sum of the other data. But i thought it is a long shoot so i decided to cover it by measures. That's 11th row I am trying to calculate and add it the matrix which has already top ten rows (i used filters)

       

      Rank320EksiHesaplar = 
                    RANKX(ALLSELECTED('Mizan Özet'[Hesap No]),
                    CALCULATE( 
                                sum('Mizan Özet'[Toplam])
                                ,'Mizan Özet'[Hesap Grubu] = "320"
                               //,filter(ALLSELECTED('Mizan Özet'), sum('Mizan Özet'[Toplam]) < 0)
                            )
                            
                            ,,1)

       

       

      I hope this clarifies my and your question.