Forum Discussion

lenawr's avatar
lenawr
New Member
1 year ago
Solved

filtered calculation changes the values

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 i...
  • Rupak_bi's avatar
    Rupak_bi
    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's avatar
v-ssriganesh
Icon for Community Support rankCommunity 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.