Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 fo...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      7 years ago

      That was the idea, yes

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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.