Average on stacked column chart

[mypowerbi1]

Hi,

the below is sample dataset of my report :

Customer No.	Priority	Business Area	Primary Focus	Secondary Focus
1	High	A	Sales	Quality
1	Low	A	Cost	Production
1	Medium	A	Size	Colour
2	Low	A	Sales	Quality
2	High	A	Size	Colour
3	High	A	Cost	Production
3	Low	A	Size	Colour
3	Low	A	Sales	Quality

I want to calculate Average Priority for Customer's peer (Average  = Count of Priority/Count of Customer's Peer) and have to show it on stacked column chart like below:

Axis : Primary Focus and Secondary Focus (Using as Hierarchy)

Legend : Priority 

Values : Average

I am using Customer No. as a slicer, so when select a Customer no. from the slicer, count of Customer will be count of all the Customer no. except the selected Customer no. from the slicer. Referring the above dataset, if select Customer no. 3 from the slicer then Count of Customer's Peer will be 2.

If i do not place priority on legend then average is correct for Primary Focus and Secondary Focus but when I place Priority on legend of stacked bar then averages for Primary Focus and Secondary Focus are not correct.

The below is my dax to calculate average:

Average =

var sel = SELECTEDVALUE('Customer'[Customer No.],BLANK())

var PeerAvg = CALCULATE( 

                                           COUNT( 'Customer'[Priority] )  / Count ('Customer'[Customer No.])

,'Customer'[Customer No.] <> sel

)

Return

PeerAvg

Please suggest how to do this to get correct average even when using Priority on legend.

Thanks

[v-yalanwu-msft · Accepted solution]

Hi, @mypowerbi1 ;

You could create another table as slicer.

slicer = VALUES(Customer[Customer No.])

Then modify the measure.

Average = 
VAR sel =
    SELECTEDVALUE ( 'slicer'[Customer No.], BLANK () )
VAR PeerAvg =
    CALCULATE ( DISTINCTCOUNT(  'Customer'[Customer No.] ),FILTER(ALL(Customer),'Customer'[Customer No.] <> sel))
RETURN
    CALCULATE(COUNT([Priority]),FILTER(ALL(Customer),[Priority]=MAX([Priority])&&[Customer No.]<>sel)) /PeerAvg

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @mypowerbi1 ;

Try it.

Average = 
VAR sel =
    SELECTEDVALUE ( 'Customer'[Customer No.], BLANK () )
VAR PeerAvg =
    CALCULATE (
        COUNT ( 'Customer'[Priority] ) / COUNT ( 'Customer'[Customer No.] ),
        FILTER(ALL(Customer),'Customer'[Customer No.] <> sel)
    )
RETURN
    PeerAvg

The final output is shown below:

I have tested your data, but am not sure about your logic and what you want to output? Can you share more details?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mypowerbi1]

Hi @v-yalanwu-msft ,

I have to show peer average on values, priority on legend and Primary focus on Axis of stacked cloumn chart.

In your file above, when selecting client no. 3 its peer count is 2 (Customer no. 1 & 2). Priority count of these peer (Customer no. 1 & 2) will be 2 count of each Low and High and 1 count of Medium. 

To calculate the average for each type of Priority, Priority count will be divided by peer count and values sould be like this:

Peer avg for Low = 1 (2/2)

Peer avg for High = 1 (2/2)

Peer avg for Medium = 0.5 (1/2)

I hope it should be more clear now.

Thanks

[v-yalanwu-msft · Accepted solution]

Hi, @mypowerbi1 ;

You could create another table as slicer.

slicer = VALUES(Customer[Customer No.])

Then modify the measure.

Average = 
VAR sel =
    SELECTEDVALUE ( 'slicer'[Customer No.], BLANK () )
VAR PeerAvg =
    CALCULATE ( DISTINCTCOUNT(  'Customer'[Customer No.] ),FILTER(ALL(Customer),'Customer'[Customer No.] <> sel))
RETURN
    CALCULATE(COUNT([Priority]),FILTER(ALL(Customer),[Priority]=MAX([Priority])&&[Customer No.]<>sel)) /PeerAvg

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mypowerbi1]

Hi @v-yalanwu-msft ,

As i am using Hierarchy on axis of the visual, the measure works on Primary Focus only but it doesn't work when drill down and check it for Secondary Focus.

 Axis : Primary Focus and Secondary Focus (Using as Hierarchy)

Thanks

[lbendlin · Accepted solution]

Use ISINSCOPE to figure out where in the hierarchy you are and adjust the measure accordingly.

[mypowerbi1]

Hi @lbendlin ,

When I am using ISINSCOPE, it gives correct values at Primary Focus level but gives incorrect values when drill down to Secondary Focus level.

The reason is, it applies a visual level filter for Primary Focus field when comes down to secondary focus. Please suggest how to adjust this Primary Focus filter in DAX when at Secondary Focus level.

Thanks

[lbendlin]

Use ISINSCOPE on the lower level first.

[lbendlin]

"I am using Customer No. as a slicer" 

Can't do that unless you do it as a standalone, disconnected table.