filtered calculation changes the values

[lenawr]

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])))

[v-ssriganesh]

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.

[v-ssriganesh]

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]

Hi @v-ssriganesh  unfortunately none of solutions worked 

[v-ssriganesh]

Hi @lenawr,

Thank you for updating us on the issue and thanks to @Rupak_bi for sharing helpful insights.

Could you confirm if @Rupak_bi's solution resolved your query? This information will assist other members facing similar challenges.

Thank you for being part of the Microsoft Fabric Community.

[Rupak_bi]

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]

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.

[FBergamaschi]

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