Hi, I have created calcualtion that is supossed to calclate % of bench and alloctaed countrywise. Now when I want to see only % of bench it calculates it as 100%. How to see filtered out bench as if there was no filter unfiltered filtered calculation is : Calculation = divide(sum([Count of Unique GGID]),CALCULATE(SUM([Count of Unique GGID]),ALL([Category_1])))

Hi lenawr , Here is your solution. The Dax Measure is as below. Per = var current_val = sum('Table'[GGID]) Var total_Val = calculate(sum('Table'[GGID]),ALLEXCEPT('Table','Table'[Country])) return DIVIDE(current_val,total_Val,0) If this works, Accept as solution, Else let me know whats going wrong.

filtered calculation changes the values | Microsoft Fabric Community

7 Replies

FBergamaschi
Super User
1 year ago
Calculation = divide(sum([Count of Unique GGID]),CALCULATE(SUM([Count of Unique GGID]),ALL([Category_1]), ALL([Country]))

If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
v-ssriganesh
Community Support
1 year ago
Hello lenawr,
Thank you for reaching out to the Microsoft Fabric Community Forum.

I have reproduced your scenario using the sample data and DAX logic you shared. I understand that you were facing an issue where, after filtering for only "Bench", your measure was returning 100% instead of showing "Bench as % of total (Bench + Allocated)".

Your original DAX measure used ALL([Category_1]), which doesn't fully override the visual filter context when you select "Bench" only. As a result, both numerator and denominator were filtered to "Bench" leading to 100%.

To ensure the calculation shows "Bench as % of total" even when filtered, I used this updated DAX:

Calculation = DIVIDE( SUM(BenchData[Count of Unique GGID]), CALCULATE( SUM(BenchData[Count of Unique GGID]), REMOVEFILTERS(BenchData[Category_1]) ) )

Output:

When viewing both Allocated and Bench, the percentages are shown per group.

When filtering only Bench, the percentages still reflect Bench as a % of total (not 100%).

I’ve attached the working .pbix file for your reference. Please feel free to download it and explore the measure and matrix visual setup.

Best regards,
Ganesh Singamshetty.

4789239.pbix19 KB
v-ssriganesh
Community Support
1 year ago
Hello lenawr,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.
- lenawr
  New Member
  1 year ago
  Hi v-ssriganesh unfortunately none of solutions worked
  - Rupak_bi
    Super User
    1 year ago
    Hi lenawr ,
    
    Here is your solution.
    
    The Dax Measure is as below.
    
    Per = var current_val = sum('Table'[GGID]) Var total_Val = calculate(sum('Table'[GGID]),ALLEXCEPT('Table','Table'[Country])) return DIVIDE(current_val,total_Val,0)
    
    If this works, Accept as solution, Else let me know whats going wrong.
v-ssriganesh
Community Support
1 year ago
Hello lenawr,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.