cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bbollmann
Frequent Visitor

Line and Clustered Column Chart with Target Percentages

Using DirectQuery, I have Plant 1 thru Plant 3 audit compliance data for which I want to show Target Pcts using the Lines on the chart.  For Plant 1 thru Plant 3, I have a SharePoint list with the Plant, a Good Percent, and a Needs Improvement Percent.

 

I'm having two issues:

1. I'm getting wavy target lines, I'm sure, due to the differing counts of the Sections/Questions when using Average or Minimum.  I'd like to see a target for all Questions/Sections to be the Average of the Plants selected.  So, if Plant 1 and Plant 3 are selected, the target line would show a Good average for all columns of 57.5%.  If only Plant 1 were selected(sliced), we'd see 55% across all columns.  What piece of DAX code would get me in that direction?

 

2. The resulting Target Lines are hovering about 15% to 20% higher than where they should be on the Y-Axis.  No idea why.

 

Any suggestions you can give would be most appreciated.

 

Here is a spreadsheet w/ the data/calculations

 

 

 

Compliance Pct = DIVIDE([Pass],[Pass]+[Fail],0)
Pass = CALCULATE(COUNTROWS(fact_audit_question),dim_audit_form_question_score[Score] = 1)
Fail = CALCULATE(COUNTROWS(fact_audit_question),ABS(dim_audit_form_question_score[Score] <> 1))

 

 

 

fact_audit_question     
LocationSectionQuestionCatScorePassFailCompliance Pct
Plant 1A10A 100010
Plant 1A20A 200010
Plant 1B10B 100010
Plant 1B20B 201101
Plant 1B20B 200010
Plant 1C20C 201101
Plant 1C20C 200010
Plant 1A10A 101101
Plant 1A10A 100010
Plant 1B20B 201101
Plant 1B10B 100010
Plant 1Z10Z 101101
Plant 1Z10Z 100010
Plant 1Z10Z 101100.5
Plant 2A10A 101101
Plant 2B10B 100010
Plant 2B10B 100010
Plant 2B10B 100010
Plant 2B20B 201100.5
Plant 2B20B 201100.5
Plant 2C20C 201100.5
Plant 2C10C 101100.5
Plant 2C20C 201101
Plant 2Z10Z 100010
Plant 2Z10Z 101100.5
Plant 2A10A 101101
Plant 3A20A 200010
Plant 3B20B 201101
Plant 3C10C 100010
Plant 3Z10Z 101101
Plant 3A20A 200010
Plant 3B10B 100010
Plant 3C10C 101100.5
Plant 3Z10Z 101101
Plant 3A10A 100010
Plant 3B20B 200010
Plant 3C20C 201100.5
Plant 3Z10Z 101100.5
Plant 3A10A 101101
Plant 3B20B 200010
Plant 3C20C 201101
Plant 3Z10Z 100010

 

dim_target_percent 
PlantGoodImprove
Plant 155%30%
Plant 250%35%
Plant 360%40%
Average55%35%

 

Pivot Table from the fact_audit_question table

Section / QuestionAvg of Compliance PctPassFail
A 1057.14%43
A 200.00%03
B 100.00%06
B 2050.00%53
C 1033.33%21
C 2066.67%51
Z 1050.00%63
 

Line and Clustered Column Chart Problem - 20210205.JPG

 

Thanks again!

3 REPLIES 3
bbollmann
Frequent Visitor

Liu,

The Good / Improvement data is accessed via an SSAS Tabular Model.  Linked together on Plant Code.

 

Does that answer your question?


@v-yangliu-msft 

v-yangliu-msft
Community Support
Community Support

Hi  @bbollmann  ,

 

Excuse me, what is the data logic of the dim_target_percent table? How are Good and Improve obtained?

 

For reports using DirectQuery, there are some restrictions on the use of the Dax function. For related content, please see this link:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about

 

Best Regards,

Liu Yang

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

bbollmann
Frequent Visitor

UPDATE:

 

I Installed the January 2021 of PBI Desktop, and problem number 2 was resolved

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors