Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Anonymous
Not applicable

The total is still grand total after filter, how to fix this problem?

Dear all,

In this Matrix, the BU will change because of the "Item" filter, and the sumx(amount) and all(BU) works perfectly as I want; however, the total of all(Item) and allexcept(BU) always show the total without any filter, basically the grand total. What I expect, instead, is the total of the yellow box.

Please help to point out which DAX function I might can use to solve this problem?
I tried few different combinations of Calculate( sumx(amount), all()/ allexcept()/ allselected()), but non of them can show the total of the yellow box. Thank you.

未命名.png

 

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

13 REPLIES 13
v-kkf-msft
Community Support
Community Support

Hi @Anonymous ,

 

Please try the following formula:

 

Measure = SUMX ( VALUES ( 'Table'[BU] ), [allexcept(BU)] )
Measure 2 = SUMX ( ALLSELECTED ( 'Table'[BU] ), [Measure] )

vkkfmsft_0-1646631748061.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

Hello @v-kkf-msft ,

Thank you for your time, but I can't open your file due to the version I used being older.
I tried the formula you shared, but maybe I misunderstood, anyway it didn't really work.
I send you a sample dataset by pm and explain more details I expected there. 
Please help to check your message box and let me know if more information is needed, thank you.

vojtechsima
Resident Rockstar
Resident Rockstar

Hi, @Anonymous ,
please share a sample dataset (copyable) so I can test my suggestions. THanks

 
Anonymous
Not applicable

Hello @vojtechsima ,
The sample dataset is sent to you by pm, please check your message box and let me know if any problem. Thank you for your time and help! 🙂

Hello, @Anonymous , 
Thank you for your dataset, this worked for me:

vojtechsima_0-1646651781796.png

 

var __SelectedBUs = VALUES('sample'[BU])

var __calc = CALCULATE(SUM('sample'[Something]),ALLEXCEPT('sample','sample'[BU]), 'sample'[BU] IN __SelectedBUs)

return __calc

 


Please note "Something" is just renamed column of "金額".

Anonymous
Not applicable

Hi,
It works well, but the problem of the column all(Item) doesn't be solved.
Could you share more? 未命名.png

 

The ideal result for me is as below if "Item AD" is selected:

BUAmount(金額)Total by BUTotal by ItemTotal by selected BU
Elle5500706892765008608966
Louis1000154003965008608966
Total6500860896665008608966

 

Because at the end I want to know the proportion as follows:

  • Amount/Total by BU
  • 1 - Amount/Total by BU
  • Total by Item/Total by selected BU 

Sorry if I didn't express the need clearly at first.

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur,

Due to the sensitive information, I can't share the PBI directly with you, but I sent you the sample dataset by pm. Please check your message and let me know if any problem, thank you.

 

ps. The column titled "金額" means amount.

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur ,

 

Thank you for the help!! This is exactly what I want!!

In case there are some people who can't open your file with the previous PBI version, I hope you don't mind I copied your formula to here.

thisisyingchi_0-1646803335939.png

Amount = SUM(Data[金額])
Total by Item = CALCULATE([Amount],ALL(Data[bu]))
Total by BU (Inter) = CALCULATE([Amount],ALLEXCEPT(Data,Data[BU]))
Total by BU = SUMX(VALUES(Data[BU]),[Total by BU (Inter)])
Measure = CALCULATE([Total by BU],ALL(Data[bu]))

 

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi, @Ashish_Mathur 
even tho I am not the author, I thank you for your answer, I was struggling to figure out the last column because I thought it wouldn't be this simple, but it is. 
So, amazing work, thank you.

You are welcome.  Thank you for your kind words.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.