Issue in total of Straight table

[shweta26]

Hi, I need implement the logic if one gcin have 1 limit id then show min of seller limit , but if 1 gcin have move than one limit id then it show  some of distinct  Seller limit  and in total it show 89,420,000. I am using this formula to display seller limit =if(Aggr(Count(DISTINCT[Seller Limit ID]),[Seller GCIN])=1,
num( ( min({<Month,Year,YearMonth,MonthNum,DateNum ={"$(vMaxDateNum)"},Flag_Lopt={1},Flag_Capt={1}>}[Seller Limit])),'#,##0'),
num( ( sum(DISTINCT{<Month,Year,YearMonth,MonthNum,DateNum ={"$(vMaxDateNum)"},Flag_Lopt={1},Flag_Capt={1}>}[Seller Limit])),'#,##0'))

But  formula show 44,710,000 in total because of different Seller code

[Chanty4u]

Hi Try this 

Sum(

    Aggr(

        If(Count(DISTINCT [Seller Limit ID]) = 1,

            Min({<Month,Year,YearMonth,MonthNum,DateNum={"$(vMaxDateNum)"},

                Flag_Lopt={1},Flag_Capt={1}>} [Seller Limit]),

            Sum(DISTINCT {<Month,Year,YearMonth,MonthNum,DateNum={"$(vMaxDateNum)"},

                Flag_Lopt={1},Flag_Capt={1}>} [Seller Limit])

        ),

        [Seller GCIN]

    )

)

[shweta26]

The formula shared above does not work correctly. While it gives the correct total value (i.e., 89,420,000), in cases where the same GCIN (row2 and 3) and same Limit ID exist but with different Seller Codes, the value becomes 0.

Seller gcin,seller code, seller limit id, sellerlimit Above formula shared by you doesnot work because it give correct value in total i.e. 89,420,000  but one of the limit id value become 0 where gcin and limit id is same but seller code is different

gcin,seller_code ,seller_limit_id,seller_limit

Total.        89,420,000
1001,S001,1001,44,710,000
1021,S002,1003,44,710,000
1021,S003,1003,0

[Daniel_Castella]

Hi @shweta26 

I would like to reproduce this issue on my end in order to help you. 

Does the data sample attached refers to what you are obtaining or what you expect to obtain?

Could you, please, attach the raw data also?

Kind Regards

Daniel

[Nagaraju_KCS]

Try this 

=if(Aggr(Count(DISTINCT [Seller Limit ID]), [Seller GCIN]) = 1,

num( Min({<Month,Year,YearMonth,MonthNum,DateNum ={"$(vMaxDateNum)"},Flag_Lopt={1},Flag_Capt={1}>} [Seller Limit]), '#,##0'),

num(
    Sum(
      Aggr(
        Only({<Month,Year,YearMonth,MonthNum,DateNum ={"$(vMaxDateNum)"},Flag_Lopt=                              {1},Flag_Capt={1}>} [Seller Limit]),                                                                              
     [Seller GCIN],
     [Seller Limit ID]
             )
          ),
   '#,##0'
        )
)

[shweta26]

Hello Daniel, 

Thanks for your response. Attached new sample data excel with expected output sheet and original data sheet.