Forum Discussion
not count unpivot extra rows
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!
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
9 Replies
v-juanli-msftCommunity Support
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
Thank you so much for providing this solution. Works perfectly.
- Greg_Deckler
Community Champion
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?
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
- 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
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!
