Forum Discussion

jiinson's avatar
jiinson
Helper I
9 years ago

Missing Total

  Dear Tutors,   I have had problem getting total values from matrix. As you can tell from the picture, total rows are empty. I have a slicer that user can choose Top N Number. When user se...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

jiinson

 

What is the total that you expected. The sum of the column in the table?

 

 

 

 

 

 

  • jiinson's avatar
    jiinson
    Helper I
    9 years ago

    Vvelarde

     

    I expect 31 for Btm PY and -11 for Btm CY.

     

    Thanks,

    Jiin Son

    • Vvelarde's avatar
      Vvelarde
      Community Champion
      9 years ago

      jiinson

       

      Ok, in this scenario you need to create the dax Part to calculate the totals, because your actual measure don't made this.

       

      I can help you with that but i need sample data to do it.

       

       

       

       

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      What is BTM?  Usually, a total will not populate if the values are not a numeric type of data. Happens to me most often when a field's value type is "ANY" instead of interger or something similar.

       

       It almost seems like the BTM is a ranking number?  If so, I do not think "rank" values can be added together? 

      • jiinson's avatar
        jiinson
        Helper I
        9 years ago

        Anonymous Vvelarde

         

        BTM incidates "Bottom". All the numbers are in Decimal Numbers. It is weird because it works finely on TopN but not Bottom N.

         

         

        Those are the dax code that I have changed and used.

        Performing

        TopN Selection = MAX('TopN'[TopN]) # I am using same slicer for TopN and BottomN.

         

        Top Rank = RANKX(ALLSELECTED(Table[Customer]), [CY Calc], , DESC)
CY Calc = CALCULATE(SUM(Table[Sales Amount]), DATESYTD('Date'[Date]))
CY Rank = IF([Top Rank] <= LOOKUPVALUE('TopN'[TopN No], 'TopN'[TopN], [TopN Selection]), [CY Calc], BLANK())
CY GM= IF(and(HASONEVALUE(DM_Costs_Std_USD_Regional[Customer]), [CY Rank]<>0) , [CY Rank]/1000,
IF([CY Rank]<>0, CALCULATE([CY Rank]/1000, FILTER(ALL(Table[Customer]), [CY Rank]<>0))))
PY GM= IF(AND([CY Rank] <>0, HASONEFILTER(Table[Customer])), CALCULATE([CY Calc], SAMEPERIODLASTYEAR('Date'[Date].[Date]))/1000, 
IF([CY Rank]<> 0, CALCULATE([CY Calc], SAMEPERIODLASTYEAR('Date'[Date].[Date]), FILTER(ALL(Table[Customer]), [CY Rank]<>0))/1000))

        Underperforming

         

        Btm Rank = RANKX(ALLSELECTED(Table[Customer]), [CY Calc], , ASC, Dense)
Btm CY Calc = IF([Btm Rank] <= LOOKUPVALUE('TopN'[TopN No], 'TopN'[TopN], [TopN Selection]), [CY Calc], BLANK())
Btm CY = IF(AND(HASONEfilter(Table[Customer]), [Btm CY Calc]<>0), [Btm CY Calc]/1000,
IF([Btm CY Calc] <>0, CALCULATE([Btm CY Calc], FILTER(ALL(Table[Customer]), [Btm CY Calc]<>0))/1000))
Btm PY = IF(AND([Btm CY Calc] <>0, HASONEFILTER(Table[Customer])), CALCULATE([CY GM Calc], SAMEPERIODLASTYEAR('Date'[Date].[Date]))/1000, 
IF([Btm CY Calc]<> 0, CALCULATE([CY Calc], SAMEPERIODLASTYEAR('Date'[Date].[Date]), FILTER(ALL(Table[Customer]), [Btm CY Calc]<>0))/1000))

         

        Thanks in advance,

        Jiin