DAX query to calculate % of total without bar graph axis slicing the measure values

[rumittal]

Hi All,

I have below kind of data(table at bottom) , where I need to calculate % of unique tests for each Priority category (Px). and plot it on bar graph (sample at bottom),

I have below DAX queries:

PriorityGroup(Axis) = SWITCH(TRUE(),TESTS[Value]<.9, "Group < 0.9",TESTS[Value]<0.98,"Group > 0.90 and < 0.98","Group >= 0.98")

PriValues(Value) = DIVIDE (
CALCULATE( DISTINCTCOUNT( TESTS[IDTest]) ),
CALCULATE ( DISTINCTCOUNT( TESTS[IDTest]), ALL ( TESTS[IDTest]))
)

But it doesn't seem to work, as denominator in 'PriValues(Value) ' is also sliced by the PriorityGroup on X-axis. so it gives only Tests for that specific priority like P1, but not all in the table.

Can you help if there is the correct way or how to achieve this ? 

Test	Value	Prioritygroup
C1	0.98	P1
C1	0.98	P1
C1	0.98	P1
C2	0.97	P1
C2	0.97	P1
C2	0.97	P1
C3	0.95	P2
C3	0.95	P2
C3	0.95	P2
C4	0.94	P2
C4	0.94	P2
C4	0.94	P2

[Icey · Accepted solution]

Hi @rumittal ,

Is this what you want?

If so, you can create your measure like so:

PriValues(Value) Measure =
DIVIDE (
    CALCULATE ( DISTINCTCOUNT ( TESTS[Test] ) ),
    CALCULATE ( DISTINCTCOUNT ( TESTS[Test] ), ALL ( TESTS ) )
)

Best Regards,
Icey

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

[rumittal]

@Icey, Thanks for the awesome suggestion,it worked wonders for me! 🙂 

For anyone who comes here, I was making a mistake on where to set ALL on, ideally it should be set on column from where you want to remove filter or entire table if thats where you want to remvoe any and all fitlers.

PriValues(Value) Measure =
DIVIDE (
    CALCULATE ( DISTINCTCOUNT ( TESTS[Test] ) ),
    CALCULATE ( DISTINCTCOUNT ( TESTS[Test] ), ALL ( TESTS ) )
)