not count unpivot extra rows

[Anonymous]

Hello,

I need to know how to not count the extra rows power bi creates when I unpivot multiple columns? 

Im working on survey data so all the questions I am unpivoting to get the responses for each question on the bottom tile. 

the other tiles have filtering enabled and drillthrough. The pie chart is count by region and since I unpivoted all the questions it created alot of extra rows and the count of region is way off. Is there a way to not count those for that tile only? 

Thanks!

[v-juanli-msft · Accepted solution]

Hi @Anonymous

I can reproduce your problem

T

To solve this, insert a step before "Unpivot columns"

add index columns from 1

then create a measure to calculate the count of regions

Measure = CALCULATE(DISTINCTCOUNT(Sheet5[Index]),ALLEXCEPT(Sheet5,Sheet5[region]))

Best Regards

Maggie

[Rose_T]

Thank you so much for providing this solution. Works perfectly. 

[Anonymous]

@v-juanli-msft

Thank you! That worked out great.  I really appreciate your help! 

[v-juanli-msft]

Hi @Anonymous

Could you show an example dataset after unpivoting columns?

Also, please change the pie chart and chart on bottom to table visual so i can see what data is in the visual.

Best Regards

Maggie

[Anonymous]

Hello @v-juanli-msft Thanks for the reply! 

Here the example of the unpivoted dataset:
This has a total of 515 rows and after unpivoting it the row count gets to 7493. Total of 65 questions and their responses. 

Dashboard data: 

Here is a comparison on the count difference:

Let me know if you need anything else. Thanks again! 

[Greg_Deckler]

Sounds like you perhaps need a measure does does a COUNT but then divides that number by the COUNT of the DISTINCT (or VALUES) number of questions?

[Anonymous]

@Greg_Deckler Thanks for the quick response! 

You are saying doing a count on the values (responses) and then divide by the count(distinct) of questions? 

Thanks!

[Greg_Deckler]

That was the idea, yes

[Anonymous]

@Greg_Deckler I tried:

Count of Value divided by Count of Attribute =

DIVIDE(COUNTA('WIOA'[Value]), DISTINCTCOUNT('WIOA'[Attribute]))

but the count is not correct, im comparing the data count and its still way off. Closer number but still not right.  

Do you have any other ideas? Thanks for the help.