Missing Total

[jiinson]

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 select TopN, there will be only N customers in the matrix. 

Those are the measures that I set up for this matrix. 

Btm CY = IF([Btm Rank] <= LOOKUPVALUE('TopN'[TopN No], 'TopN'[TopN], [TopN Selection]), [CY Calc]/1000, BLANK())

Btm PY =

IF(AND([Btm CY] <>0, HASONEFILTER(DM_Costs_Std_USD_Regional[Customer])),

CALCULATE([CY Calc], SAMEPERIODLASTYEAR('Date'[Date].[Date]))/1000,
IF([Btm CY]<> 0, CALCULATE([CY Calc], SAMEPERIODLASTYEAR('Date'[Date].[Date]),

FILTER(ALL(Table[Customer]), [Btm CY]<>0))/1000))

What are the reasons that I can't get total value. 

Thanks in advance.

Best Regards,

Jiin Son

[Vvelarde]

@jiinson

hi,

To test purposes please change 

Btm CY = IF([Btm Rank] <= LOOKUPVALUE('TopN'[TopN No], 'TopN'[TopN], [TopN Selection]), [CY Calc]/1000, BLANK())

to

Btm CY = IF([Btm Rank] <= LOOKUPVALUE('TopN'[TopN No], 'TopN'[TopN], [TopN Selection]), [CY Calc]/1000, 123456)

What is the result

[jiinson]

@Vvelarde

The result is this. 

[Vvelarde]

@jiinson

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

[jiinson]

@Vvelarde

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

Thanks,

Jiin Son

[Anonymous]

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]

@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 

[v-huizhn-msft]

Hi @jiinson,

I am unable to reproduce your scenario using my sample data on my local computer. Is there any chance to share your .pbix file for further analysis?

Best Regards,
Angelia

[Vvelarde]

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