Forum Discussion

Shinu1's avatar
Shinu1
Helper II
6 years ago

Incorrect Total / Missing Total based on Ranking

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 ...
Need Help
v-xicai's avatar
v-xicai
Community Support
6 years ago

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's avatar
Shinu1
Helper II
6 years ago

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