Solved: Value Count after Unpivot Columns is not unique

TinyElephant · ‎08-02-2021

I have some employee data in Excel table containing experience and software skills. I applied Unpivot Columns of both Experience and Software Skills separately and plotted them. The percentage/proportion is coming out correct but the count is exaggerated. Please suggest.

Employee	1-3 years	4-8 years	9+ years	Software 1	Software 2	Software 3
John		Y		Y		Y
Derek		Y		Y	Y
Michael	Y
William			Y	Y	Y	Y

v-jayw-msft · ‎08-04-2021

Hi @TinyElephant ,

Step1: unpivot the year columns.

Step2: filter experience column which value is blank.

Step3: unpivot the software columns.

Pbix as attached.

Best regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

v-jayw-msft · ‎08-04-2021

Hi @TinyElephant ,

Step1: unpivot the year columns.

Step2: filter experience column which value is blank.

Step3: unpivot the software columns.

Pbix as attached.

Best regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

TinyElephant · ‎08-05-2021

Thanks. Any suggestions for the case when I have multiple such unpivoted headers and their values?

TinyElephant · ‎08-03-2021

Any other way to represent the excel data would also be fine.

Greg_Deckler · ‎08-02-2021

@TinyElephant You can use:

Count Measure = COUNTROWS(DISTINCT('Table'[Employee]))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...