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
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.
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!
That was the idea, yes
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!
