Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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?
@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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |