Adding Average Point on a cluster bar chart

[Anonymous]

I am creating a cluster bar chart utilizing a custom table to drive the clusters in the Axis and a measure using a switch to drive which measure is behind each cluster.  I am looking to add an average line or diamond(Excel), last image attached, but running into the can't create an average measure on a measure.   

I am also attaching images of my PowerBI graph and its parameters.   

GraphRate is a single column table that has a label for each of the Clusters.  SentYYYYMM is the date driving each bar in the cluster.   GraphDM is the measure that switches the measure used based on the value in GraphRate column.   I can put an Average line but it averages across all clusters and not to each cluster.  Any help greatley appreciated.

[Anonymous]

Hi @Anonymous,

Perhaps you can switch to the line and clustered column chart and write a measure to show the summarize point on the top of the clustered bar. (setting the filter on the current x-axis group to prevent power bi link these points)

If you confused about the coding formula, please share some dummy data and paste it here with table format then we can simply copy them to test.
Regards,

Xiaoxin Sheng

[parry2k]

@Anonymous sorry not sure I can do anything with these details. Good luck. If you cannot put a sample data together to get help, not sure how I can assist. Sorry about that. I hope someone else can take a look and get you over the line. 

[Anonymous]

Appreciate the offer hopefully someone else has been here.

[parry2k]

@Anonymous  what DAX measure you are using for average? Provide sample pbix file with expected output might be an easier way to provide a solution for this. Share thru one drive/google drive.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi,

Due to size and sensitivity of the data, I can't share the PBIX.   

The Axis is controlled by a table

The legend is a text field from a header table that has a date converted to YYYYMM.

In Values is a Measure that is set to the value of another measure based on measure Graph1m.  Graph1m has following logic:  

Graph1M = max(GraphRates[GraphRate])

GraphDM = SWITCH(

TRUE()

,CONTAINSSTRING([Graph1M],"Un-Deliverable Rate"), [RateUndeliverable%]

,CONTAINSSTRING([Graph1M],"Deliverable Rate"), [RateDeliverable%]

,CONTAINSSTRING([Graph1M],"Invite Click Thru Rate"), [RateInviteClickThru%]

,CONTAINSSTRING([Graph1M],"Survey Click Thru Rate"), [RateSurveyClickThru%]

,CONTAINSSTRING([Graph1M],"Non-Response Rate"), [RateNonResponse%]

,CONTAINSSTRING([Graph1M],"Survey Response Rate"), [RateResponse%]

,CONTAINSSTRING([Graph1M],"Complete Rate"), [RateContactCompleteQ%]

,CONTAINSSTRING([Graph1M],"Partial Rate"), [RateContactPartialCompleteQ%]

So a cluster grouping is set to a measure value across YYYYMM.   What I am looking to do is set an average for each cluster potentially adding a trend line across each cluster.  The Analytics tab allows you to set min,max,media, average, percentile, constant but that is for the entire graph.