Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | |||||||
Location | Section | Question | Cat | Score | Pass | Fail | Compliance Pct |
Plant 1 | A | 10 | A 10 | 0 | 0 | 1 | 0 |
Plant 1 | A | 20 | A 20 | 0 | 0 | 1 | 0 |
Plant 1 | B | 10 | B 10 | 0 | 0 | 1 | 0 |
Plant 1 | B | 20 | B 20 | 1 | 1 | 0 | 1 |
Plant 1 | B | 20 | B 20 | 0 | 0 | 1 | 0 |
Plant 1 | C | 20 | C 20 | 1 | 1 | 0 | 1 |
Plant 1 | C | 20 | C 20 | 0 | 0 | 1 | 0 |
Plant 1 | A | 10 | A 10 | 1 | 1 | 0 | 1 |
Plant 1 | A | 10 | A 10 | 0 | 0 | 1 | 0 |
Plant 1 | B | 20 | B 20 | 1 | 1 | 0 | 1 |
Plant 1 | B | 10 | B 10 | 0 | 0 | 1 | 0 |
Plant 1 | Z | 10 | Z 10 | 1 | 1 | 0 | 1 |
Plant 1 | Z | 10 | Z 10 | 0 | 0 | 1 | 0 |
Plant 1 | Z | 10 | Z 10 | 1 | 1 | 0 | 0.5 |
Plant 2 | A | 10 | A 10 | 1 | 1 | 0 | 1 |
Plant 2 | B | 10 | B 10 | 0 | 0 | 1 | 0 |
Plant 2 | B | 10 | B 10 | 0 | 0 | 1 | 0 |
Plant 2 | B | 10 | B 10 | 0 | 0 | 1 | 0 |
Plant 2 | B | 20 | B 20 | 1 | 1 | 0 | 0.5 |
Plant 2 | B | 20 | B 20 | 1 | 1 | 0 | 0.5 |
Plant 2 | C | 20 | C 20 | 1 | 1 | 0 | 0.5 |
Plant 2 | C | 10 | C 10 | 1 | 1 | 0 | 0.5 |
Plant 2 | C | 20 | C 20 | 1 | 1 | 0 | 1 |
Plant 2 | Z | 10 | Z 10 | 0 | 0 | 1 | 0 |
Plant 2 | Z | 10 | Z 10 | 1 | 1 | 0 | 0.5 |
Plant 2 | A | 10 | A 10 | 1 | 1 | 0 | 1 |
Plant 3 | A | 20 | A 20 | 0 | 0 | 1 | 0 |
Plant 3 | B | 20 | B 20 | 1 | 1 | 0 | 1 |
Plant 3 | C | 10 | C 10 | 0 | 0 | 1 | 0 |
Plant 3 | Z | 10 | Z 10 | 1 | 1 | 0 | 1 |
Plant 3 | A | 20 | A 20 | 0 | 0 | 1 | 0 |
Plant 3 | B | 10 | B 10 | 0 | 0 | 1 | 0 |
Plant 3 | C | 10 | C 10 | 1 | 1 | 0 | 0.5 |
Plant 3 | Z | 10 | Z 10 | 1 | 1 | 0 | 1 |
Plant 3 | A | 10 | A 10 | 0 | 0 | 1 | 0 |
Plant 3 | B | 20 | B 20 | 0 | 0 | 1 | 0 |
Plant 3 | C | 20 | C 20 | 1 | 1 | 0 | 0.5 |
Plant 3 | Z | 10 | Z 10 | 1 | 1 | 0 | 0.5 |
Plant 3 | A | 10 | A 10 | 1 | 1 | 0 | 1 |
Plant 3 | B | 20 | B 20 | 0 | 0 | 1 | 0 |
Plant 3 | C | 20 | C 20 | 1 | 1 | 0 | 1 |
Plant 3 | Z | 10 | Z 10 | 0 | 0 | 1 | 0 |
dim_target_percent | ||
Plant | Good | Improve |
Plant 1 | 55% | 30% |
Plant 2 | 50% | 35% |
Plant 3 | 60% | 40% |
Average | 55% | 35% |
Pivot Table from the fact_audit_question table
Section / Question | Avg of Compliance Pct | Pass | Fail |
A 10 | 57.14% | 4 | 3 |
A 20 | 0.00% | 0 | 3 |
B 10 | 0.00% | 0 | 6 |
B 20 | 50.00% | 5 | 3 |
C 10 | 33.33% | 2 | 1 |
C 20 | 66.67% | 5 | 1 |
Z 10 | 50.00% | 6 | 3 |
Thanks again!
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
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.
UPDATE:
I Installed the January 2021 of PBI Desktop, and problem number 2 was resolved
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |