Incorrect Total / Missing Total based on Ranking

[Shinu1]

Hi Team,

I'm not able to get the measure total correct after ranking based on another measure.

Let me make it more clear:

I have below given matrix table with details. Ranking is done based on Variance column

Accounts	Current Year	Previous Year	Variance	Ranking
A	100	90	10	2
B	67	77	-10	3
C	88	33	55	1
D	12	79	-67	4
Total	267	279	-12

Now, my problem starts when the users wants to see only Top 2 line items based on Variance.

Below is the output that I'm looking for

Accounts	Current Year	Previous Year	Variance	Ranking
A	100	90	10	2
C	88	33	55	1
Total	188	123	65

Below is the output that I'm getting. The Total of Current Year and Previous Year are incorrect. They are same as the entire colum total.

Accounts	Current Year	Previous Year	Variance	Ranking
A	100	90	10	2
C	88	33	55	1
Total	267	279	65

Current Year:

var _maxYr = CALCULATE(Max(DateCalendar[Year]),ALL(DateCalendar))

return
CALCULATE([ExternalRevenue],FILTER(ALL(DateCalendar), DateCalendar[Year] =_maxYr))

Previous Year:

var _minYr = CALCULATE(Min(DateCalendar[Year]),ALL(DateCalendar))

return
CALCULATE([ExternalRevenue],FILTER(ALL(DateCalendar), DateCalendar[Year] =_minYr))

Ranking:

RANKX(ALL(tbl_Processed_Rawdata[Accounts]),[Variance])

Variance:

Variance = [Current Year]-[Previous Year]

Measure Used to Populate Current Year based on ranking:

Current Year1 =

var _maxYr = CALCULATE(Max(DateCalendar[Year]),ALL(DateCalendar))

Var CY = CALCULATE([ExternalRevenue],FILTER(ALL(DateCalendar), DateCalendar[Year] =_maxYr))

var SelectedTop = SELECTEDVALUE('TopN'[TopN]) // lets say user selected 2

Var RankVariance = RANKX(ALL(tbl_Processed_Rawdata[Accounts]),[Variance])

Var CY_topfiltered = if(RankVariance <= SelectedTop,CY,BLANK())

return

if(

HASONEFILTER(tbl_Processed_Rawdata[Accounts]),if(RankVariance <= SelectedTop,CY,BLANK()),

CALCULATE(CY,

FILTER(ALLSELECTED(tbl_Processed_Rawdata[Accounts]),

RankVariance <= SelectedTop))

)

// The next formula I tried was below, this does not show the total at all

if(

HASONEFILTER(tbl_Processed_Rawdata[Accounts]),if(RankVariance <= SelectedTop,CY,BLANK()),

SUMX(DISTINCT(tbl_Processed_Rawdata[Accounts]),CY_topfiltered )

)

// The next formula I tried was below, this does not show the total at all

SUMX(ALL(tbl_Processed_Rawdata[Accounts]),

CALCULATE(DISTINCTCOUNT(tbl_Processed_Rawdata[Accounts]),

FILTER(VALUES(tbl_Processed_Rawdata[Accounts]),CY_topfiltered))

// The next formula I tried was below, this does not show the total at all

CALCULATE(

SUMX (ALL ( tbl_Processed_Rawdata[Accounts] ),

IF ( RankVariance <= SelectedTop, CY,BLANK() )

)

)

// Even this one didnt work.

SUMX(VALUES(tbl_Processed_Rawdata[Accounts]),

SWITCH(TRUE(),

SelectedTop = 0,CY,

CALCULATE(

RANKX(

ALL(tbl_Processed_Rawdata[Accounts]),

CY)<=SelectedTop,ALLSELECTED(DateCalendar)

),CY

))

Please help me to get the total correct for Top 2 selection based on varinace ranking.

[Ashish_Mathur]

Hi,

Share a small sample dataset, describe the business question and show the expected result.  I canot understand why your measure for Current Year_1 and Previous Year_1 are so lengthy and complicated.

[Shinu1]

Hi Ashish,

I have mentioned my business requirement as the first posted messages in detail. I have mentioned the output that i'm looking for and the incorrect output that I'm getting. In short, on the basis of variance ranking , using Top N slicer, i need to see the TopN line items in the table, which is working fine except that the totals are coming incorrect. I have also uploaded a sample file in one of the messages.

Here is the link once again: https://drive.google.com/open?id=1XnFSGZSFdGvI6B9k1KNC40q2O8xZW0HN

To make the picture clear, The matrix table shows, data for current year and previous year and their variance. Variance is then ranked, highest number being 1. I have used Top N  slicer connected to this ranking so that the user can select the number of variance he wants to see. All this is working fine except for the totals being shown is incorrect. All I need is the totals to be correct based on the TopN slicer selection.

Hope you can help me

Thanks in advance

Shinu

[Shinu1]

 Hi All,

After doing a lot of research, i'm able to solve the issue. Things to keep in mind while working with Variables is that

1. If you have a measure used in Vairable , the grand total will either not display or show the entire table total instead of visible rows total and hence they must be stored as separate measures instead in a variable.

Below Example: I stored the measures [ExternalRevenue] and [Variance] inside a variable. This resulted in showing entire grand total/some time totals disappeared

Var CY = CALCULATE([ExternalRevenue],FILTER(ALL(DateCalendar), DateCalendar[Year] =_maxYr))

Var RankVariance = RANKX(ALL(tbl_Processed_Rawdata[Accounts]),[Variance])

Solution:

Create a separate measure 

var _maxYr = CALCULATE(Max(DateCalendar[Year]),ALL(DateCalendar))
1. [CY_ER] =  CALCULATE([ExternalRevenue],FILTER(ALL(DateCalendar), DateCalendar[Year] =_maxYr))

2. [RankVariance_1] = RANKX(ALL(tbl_Processed_Rawdata[Accounts]),[Variance])

And a small change in my Calculation formula that made it work:

CALCULATE([CY_ER],FILTER(ALL('tbl_Processed_Rawdata'[Accounts]), [RankVariance]<=SelectedTop),DISTINCT('tbl_Processed_Rawdata'[Accounts]))

Below is the sample working file for your reference. 

Working Sample File 

Thanks

Shinu

[v-xicai]

Hi @Shinu1 ,

You may create measures like DAX below.

Current Year _New=

VAR _table = SUMMARIZE('tbl_Processed_Rawdata', 'tbl_Processed_Rawdata'[Accountsl], "_Value", [Current Year])

RETURN

IF(HASONEVALUE('tbl_Processed_Rawdata'[Accounts]), [Current Year], SUMX(_table,[_Value]))



Previous Year _New=

VAR _table = SUMMARIZE('tbl_Processed_Rawdata', 'tbl_Processed_Rawdata'[Accountsl], "_Value", [Previous Year])

RETURN

IF(HASONEVALUE('tbl_Processed_Rawdata'[Accounts]), [Previous Year], SUMX(_table,[_Value]))

For reference:

https://community.powerbi.com/t5/Desktop/Maximum-value-and-total-by-group/m-p/727857#M351273 ,

https://community.powerbi.com/t5/Desktop/sumx-row-and-measur/m-p/775243#M373460 ,

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907 .

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shinu1]

Hi,
Thanks for the reply. The measure provided by you does not show the data based on Variance ranking. If user wants to see Top 2 line items with variance, the table should show only two line items. This process is missing in your measure exmaple. In the sample file provided by me, the ranking function is working but the totals are coming incorrect.

Could you please look into it once again.

Thanks

Shinu

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[arehman]

provide a PBIX file to solve this issue by looking as far i see you are not adding current and previous rows value to get total for example 

your total iteration=Total number of accounts

than do first iteration 

current total=current year value row

also set 

previous total =current total

than next iteration 

current total =previous total +current year value 

Total =current total 

[sanimesa]

@Shinu1  If your calendar table contains dates beyond current year, it could mess up you calculation. In this case I'd use TODAY() function to extact the current year. 

[edhans]

It would be a lot easier to assist if you could provide a PBIX file with no confidential info, otherwise you are asking us to recreate all of your tables and measures. 😯

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Shinu1]

Here is the link to sample file: https://drive.google.com/open?id=1XnFSGZSFdGvI6B9k1KNC40q2O8xZW0HN