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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Filter is not working with All SELECTED

Hi All,

I need your help with dax powerbi. I have difficulty solving for this hours but couldnt solve
I have measure which divides the sum of sku/total sum of sku to get the percentage.

Measure
SKU Stock Contribution (%) = DIVIDE(
SUM(Query1[stock_stock_value]),
CALCULATE(SUM(Query1[stock_stock_value]),ALLSELECTED(Query1)))

Problem:
There are three filters that I used date, product and distrirbutor filter but whenever I used the product filter the values changes to 100%. This only happens when I used the product filter and this only affects with this Measure other measures are not affected. I'm guessing its because of the "ALLSELECTED" 

Need your help
Screenshot_1.pngScreenshot_2.png

 

1 ACCEPTED SOLUTION
harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

Let me know if this measures work.

 

Total SKU SUM DIST = SUM(Data14[TOTAL SKU Value])
 
SUM SKU by Dist =
CALCULATE(SUMX(Data13,Data13[SKU]),ALLEXCEPT(Data14,Data14[Distrubutor Name]))
 
SKU Stock Contribution(%) = Divide([SUM SKU by Dist],[Total SKU SUM DIST],0)
 
Data13 is ChartTable1 and Data14 is ChartTable2
I added some more dummy values to validate my results and it seems to work fine.
 
SKU_1.JPGSKU_2.JPGSKU_3.JPGSKU_4.JPG
 
Regards,
Harsh Nathani
 
 

View solution in original post

10 REPLIES 10
harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

Let me know if this measures work.

 

Total SKU SUM DIST = SUM(Data14[TOTAL SKU Value])
 
SUM SKU by Dist =
CALCULATE(SUMX(Data13,Data13[SKU]),ALLEXCEPT(Data14,Data14[Distrubutor Name]))
 
SKU Stock Contribution(%) = Divide([SUM SKU by Dist],[Total SKU SUM DIST],0)
 
Data13 is ChartTable1 and Data14 is ChartTable2
I added some more dummy values to validate my results and it seems to work fine.
 
SKU_1.JPGSKU_2.JPGSKU_3.JPGSKU_4.JPG
 
Regards,
Harsh Nathani
 
 
Greg_Deckler
Super User
Super User

You might consider replacing ALLSELECTED with an ALLEXCEPT or REMOVEFILTERS. Not certain what filters you want to keep and what you want to remove for your "ALL" part of your calculation.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi @Greg_Deckler,

I'm not really sure with the "ALL SELECTED" but I cannot get the percentage of sku sum/total sku sum without it. If I remove it the result is 100% all data.

Screenshot_4.png

@Anonymous  - Sample data posted as text to recreate would really help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

It sounds like you have a SKU. And SKU's are grouped into Groups. You have some other dimensions. You want to calculate a particular SKU's impact on a Group keeping your other dimension selections such as date, etc.

 

That is a fairly sophisticated calculation and depends greatly on exactly how your data is laid out.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Try ALLSELECTED() without specifying a table.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

 

Genius!
Anonymous
Not applicable

Hi @Mariusz, I can see the 100% whenever I selected the product 

Screenshot_3.png

Hi @Anonymous 

 

What would you expect to see, if you expecting to see the percentage of total regardless of the filter context then you can use ALL() instead.

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Anonymous
Not applicable

Hi  @Greg_Deckler  @Mariusz, Thank you for tryring to help me
Here is an example of my data
I have  Table A columns  SKU Product, SKU Value, SKU Distributor, Sku date 

I put SKU Product and SKU value in table 1 then I used product, distributor and date as a filter.

Chart Table 1:
SKU NAME             SKU VALUE          Distributor
SKU A                      24                         Distrubutor A   
SKU B                       20                         Distrubutor A   
SKU C                      30                          Distrubutor B

Chart Table 2: 
Distrubutor Name             TOTAL SKU VALUE
Distrubutor A                      180
Distrubutor B                       200
Distirbutor C                        300

I used the same field/column called Sku_Value in both tables the difference is in first table I used product and in second is distributor. The measure that I need to do is get the percentage of the sku
Formula: SKU %=  SKU Value(Chart Table 1) / Total SKU value(Chart Table 2)  * 100 %.


Hence I have the measure: 

SKU Stock Contribution (%) = DIVIDE(
SUM(Query1[stock_stock_value]),
CALCULATE(SUM(Query1[stock_stock_value]), ALLSELECTED(Query1))).

Problem is i'm not too sure with the "allselected" it causes an issue in the product filter only.

Hope this helps, Thank you so much  






Greg_Deckler
Super User
Super User

Could be. Could be a missing relationship. Could be a few other things. Would need to see how your data is laid out, is everything in a single table? Can you provide sample data to reproduce the problem?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors