Forum Discussion

HemanthV's avatar
HemanthV
Helper II
4 years ago
Solved

ALLEXCEPT is not working for virtual column

Hi Guys,

 

I am facing issue to get denominator to calculate percentage, i don't have any issue with Numerator since it is just a distinct count of projects but i am facing issue with denominator. I need to sum the count for individual category.

 

Denominator =
var virtable = SUMMARIZE('Table','Table'[Axis],'Table'[Value],"Count", DISTINCTCOUNT('Table'[Project Name]))
var denominator = CALCULATE( SUMX(virtable,[Count]), ALLEXCEPT('Table', 'Table'[Axis]))
return
denominator
 
I have created a virtual table and i am trying to add the "Count" column but ALLEXCEPT function is not working as expected here.
 
Expected Output:
Please help me on this!
Thanks in advance!
 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi HemanthV ,

     

    I'd suggest you create a new table and then create a measure separately:

    Summarized = SUMMARIZE('Table','Table'[Axis],'Table'[Value],"Count", DISTINCTCOUNT('Table'[Project Name]))
    Measure = CALCULATE(SUM(Summarized[Count]),FILTER('Summarized',[Axis]=MAX('Table'[Axis])))

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    HemanthV , change this and try

    var denominator = CALCULATE( SUMX(filter(virtable,[Axis] =max([Axis])),[Count]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi HemanthV ,

     

    I'd suggest you create a new table and then create a measure separately:

    Summarized = SUMMARIZE('Table','Table'[Axis],'Table'[Value],"Count", DISTINCTCOUNT('Table'[Project Name]))
    Measure = CALCULATE(SUM(Summarized[Count]),FILTER('Summarized',[Axis]=MAX('Table'[Axis])))

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.