ALLEXCEPT is not working for virtual column

[HemanthV]

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 find the attached pbix with sample data

https://drive.google.com/file/d/1grYgcTwNLzWdZdB4gLPwbpH23_NwcQb6/view?usp=sharing

Please help me on this!

Thanks in advance!

[v-eqin-msft · Accepted solution]

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.

[amitchandak]

@HemanthV , change this and try

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

[HemanthV]

Hi @amitchandak ,

Thanks for your reply. I have tried it, but it did not worked.

I have provided sample pbix, can you please try on that file?

Please find the attached pbix with sample data

https://drive.google.com/file/d/1grYgcTwNLzWdZdB4gLPwbpH23_NwcQb6/view?usp=sharing