How to arrive Trendline values and it's percentage using line and stacked column chart.

[saivina2920]

I tried to bring the trend line with below condition. but, i am not sure how to derive the below formula.

can anyone help to derive the below formula in my chart. 

Sample file Attached with data for analysis : https://1drv.ms/u/s!AiSRcgO5FUmN8V4GjoeHBWxo-nll?e=9x6uDE 

Condition is below

If check box Checked
A count = Total count of (DEPT-A,DEPT-B,DEPT-C,DEPT-D) which contains "EXCELLENT" values for Unique of RECID
B count = Total Count of Respective Dept Column which contains "EXCELLENT" values for Unique of RECID
Line value and it's percentage% = (A / B) * 500
Bar – Orange - Unique of RECID which contains “EXCELLENT” in mentioned Respective Dept column
Bar – Blue - Unique of RECID which contains “NOT IN LIST” in mentioned Respective Dept column

If check box is not Checked
A count = Total count of (DEPT-A,DEPT-B,DEPT-C,DEPT-D) which contains "EXCELLENT" values for Unique of RECID and “REGION” and “VALUE” column values are blank
B count = Total Count of Respective Dept Column which contains "EXCELLENT" values for Unique of RECID
Line value and it's percentage% = (A / B) * 500
Bar – Yellow - Unique of RECID which contains “EXCELLENT” in mentioned Respective Dept column
Bar – Blue - Unique of RECID which contains “NOT IN LIST” in mentioned Respective Dept column

NOTE : All Filters should based on Category

Current Output

Expected Output

[v-jingzhang]

Hi @saivina2920 

The sample file is not available, could you update the link?

Jing

[saivina2920]

can you pls. update for the same...?

[saivina2920]

Requirement formula has updated in the query for easy understanding.

pls. let me know if you have any queries.. sample file is the same..

[PaulDBrown]

Can you clarify what you mean by "FAMALE", " REGION", RECID... given the sample data in the file?

[saivina2920]

I just confusing to handle check box cases..pls. let me know if you need any more details

[saivina2920]

Sorry for the incorrect data.  pls. consider "EXCELLENT" instaed of "FEMALE"

I have uploaded the new file is here with correct Data  : https://1drv.ms/u/s!AiSRcgO5FUmN8V4GjoeHBWxo-nll?e=9x6uDE  

RECID is the key field and it contains duplicate rows. This is the First filter to get the unique rows after selecting the category.

REGION and VALUE column are used as mentioned below.

If check box Checked
A count = Total count of (DEPT-A,DEPT-B,DEPT-C,DEPT-D) which contains "EXCELLENT" values for Unique of RECID
B count = Total Count of Respective Dept Column which contains "EXCELLENT" values for Unique of RECID
Line value and it's percentage% = (A count / B count) * 500
Bar – Orange - Unique of RECID which contains “EXCELLENT” in mentioned Respective Dept column
Bar – Blue - Unique of RECID which contains “NOT IN LIST” in mentioned Respective Dept column

If check box is not Checked
A count = Total count of (DEPT-A,DEPT-B,DEPT-C,DEPT-D) which contains "EXCELLENT" values for Unique of RECID and “REGION” and “VALUE” column values are blank
B count = Total Count of Respective Dept Column which contains "EXCELLENT" values for Unique of RECID
Line value and it's percentage% = (A count / B count) * 500
Bar – Yellow - Unique of RECID which contains “EXCELLENT” in mentioned Respective Dept column
Bar – Blue - Unique of RECID which contains “NOT IN LIST” in mentioned Respective Dept column

Expected Output

pls. let me know if you need any more details

[v-jingzhang]

Hi @saivina2920 

The count results should be calculated with measures. I create the following measures according to your descriptions. Let's first check whether their results are correct. 

A Count = CALCULATE(COUNT('Table'[legend]),'Table'[Value2]="EXCELLENT",ALL('Table'[legend]))

B Count = CALCULATE(COUNT('Table'[legend]),'Table'[Value2]="EXCELLENT")

Line percentage% = DIVIDE([A Count],[B Count])*500

Orange Count = CALCULATE(DISTINCTCOUNT('Table'[RECID]),'Table'[Value2]="EXCELLENT")

Blue Count = CALCULATE(DISTINCTCOUNT('Table'[RECID]),'Table'[Value2]="NOT IN LIST")

This is based on the latest sample data you provided. And I unpivot (DEPT-A,DEPT-B,DEPT-C,DEPT-D) columns in Power Query Editor to transform the table into below one. 

Let me know if the measures get the correct results. We need to ensure the calculation is correct before we use them in a column and line chart. If they are not correct, what is the expected result based on the sample data?

Jing

[saivina2920]

Hi @v-jingzhang,

Thanks for your great support.

I have updated my logic and data. If you have any doubts pls. let me know..

[v-jingzhang]

Hi @saivina2920 

I think I nearly understand but still have two questions to confirm. 

1. When check box Checked, either Region or Value could contain blank, right? When check box unchecked, both Region and Value should be blank, right?

2. What does "TC" mean in the last sentence?

Jing

[saivina2920]

Hi @v-jingzhang ,

Any update about my reply...

[v-jingzhang · Accepted solution]

Hi @saivina2920 

Please download the attached pbix to see whether it meets your need. 

Jing

[saivina2920]

Hi @v-jingzhang ,

Thanks for your extradinary result..

your Attached sample file contains some measure not using.

Can you pls. give the final file which contains used measures and tables..?

So that it will be clear for easy understaning and reference.

pls. tell us the highlighted is really required for the calculation...

[v-jingzhang]

Hi @saivina2920 

All the measures are used in the report. Some of them are used in other measures and are not used in a visual directly.

For example, [Line percentage%] and [Line percentage% Not checked] are used in [Line %]. [Line %] is the one used in the visual. 

And [A count checked] & [A Count Not Checked] & [B Count] are used in [Line percentage%] and [Line percentage% Not checked]. 

You can hide these measures if you don't need to use them in visuals directly. But you cannot delete them. 

Jing

[saivina2920]

Hi @v-jingzhang ,

Can you pls. update the last doubts...?

[saivina2920]

One small doubts.. we are unpivot the coulmns for measures.

If we are unpivot the columns, the original column structure has changed.

Because of this, original column has changed and the other chart control not showing properly in this data model.

Is it possible to maintain all the columns with unpivot columns.

pls. give us suggestions and this is urgent..

[saivina2920]

Thanks for your answer...It is reasonable..

[saivina2920]

Yes..Thanks..It's awesome...It meets the requirement...

[saivina2920]

Hi @v-jingzhang ,

Any doubt about my explanation...pls. let me know if you need any more details...

[saivina2920]

Thanks for your question. pl. find updated data : https://1drv.ms/u/s!AiSRcgO5FUmN8WGHHt5CGND4QMbi?e=4IXetx 

1. When check box Checked, either Region or Value could contain blank, right? When check box unchecked, both Region and Value should be blank, right?

Answer :

When check box checked : No need to add this column (Region and Value) condition in the formula. 

Ex : Total count of Rows (DEPT-A,DEPT-B,DEPT-C,DEPT-D) which contains "EXCELLENT" values.

When check box NOT checked : we need to add this column (both Region and Value should be blank) condition in the formula.

Ex : Total count of Rows (DEPT-A,DEPT-B,DEPT-C,DEPT-D) which contains "EXCELLENT"
values and REGION and VALUE is blank.

2. What does "TC" mean in the last sentence?

"TC" is equivalent to "EXCELLENT" . 

so the condition should be ==> Total count of Rows (DEPT-A,DEPT-B,DEPT-C,DEPT-D) which contains "TC" or "EXCELLENT" values.

Either we can add "TC" with "EXCELLENT". (if all three values are "EXCELLENT" and one value is "TC" then it is "TRUE").

Either  we can use "TC" or "EXCELLENT" Or we can omit "TC". both are same...

[saivina2920]

Thanks for your reply..

Based on the sample data (Modified data for testing and its count),

updated file here : https://1drv.ms/u/s!AiSRcgO5FUmN8V9CWBMzyrc3SXlY?e=kIp2xd 
1. First selecting Categroy "TYPE-A"
2. a. If check box Checked,
Check all the 4 columns which contains "EXCELLENT" values, then
A count = Total count of Rows (DEPT-A,DEPT-B,DEPT-C,DEPT-D) which contains "EXCELLENT" values. ==> 1
B count = Total Count of Respective (Dept-A) Column which contains "EXCELLENT" values. ==> 5
Line value and it's percentage% = (A count / B count) * 500 ==> (1/5)*500
Bar – Orange - Total count of rows which contains “EXCELLENT” in Respective (Dept-A) column ==> 5
Bar – Blue - Total count of rows which contains “NOT IN LIST” in Respective (Dept-A) column ==> 2

2. b. If check box Not Checked,
Check all the 4 columns which contains "EXCELLENT" values, then
A count = Total count of Rows (DEPT-A,DEPT-B,DEPT-C,DEPT-D) which contains "EXCELLENT"
values and REGION and VALUE is blank. ==> 1
B count = Total Count of Respective Dept-A Column which contains "EXCELLENT" values. ==> 5
Line value and it's percentage% = (A count / B count) * 500 ==> (1/5)*500
Bar – Orange - Total count of rows which contains “EXCELLENT” in Respective Dept-A column ==> 5
Bar – Blue - Total count of rows which contains “NOT IN LIST” in Respective Dept-A column ==> 2

Note : which contains "TC" then exclude the column and check remaining column as it is.

For RECID,
Anyhow it will be unique for every category. no need to consider distinct. we can directly take DEPT values count.

Orange Count = CALCULATE(DISTINCTCOUNT('Table'[RECID]),'Table'[Value2]="EXCELLENT")

Blue Count = CALCULATE(DISTINCTCOUNT('Table'[RECID]),'Table'[Value2]="NOT IN LIST")

Final output bar chart should display as Dept wise.