Forum Discussion
Average on stacked column chart
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:
Please suggest how to do this to get correct average even when using Priority on legend.
Thanks
Hi, Anonymous ;
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)) /PeerAvgThe 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.Use ISINSCOPE to figure out where in the hierarchy you are and adjust the measure accordingly.
8 Replies
"I am using Customer No. as a slicer"
Can't do that unless you do it as a standalone, disconnected table.
Hi, Anonymous ;
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 PeerAvgThe 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.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
Hi, Anonymous ;
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)) /PeerAvgThe 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.