Questionnaire data has blank entries. How to sum in Power Pivot?

[MAYNAARD]

Please excuse a newbe question.

I have a lot of questionnaire data that Excel can count and sum and average despite blanks. But it doesn't produce BI reports.

In power pivot in Excel 2013 I have created for a range of question columns three calculated fields, in shorthand = count all, count blanks and count = "1".

Then, a calc field [count1]/([countall]-[countblank]) which gives %age satisfaction.

it works fine but is tedious to write four calc fields for each question when only a the field name changes.

Is there a way of either:

dealing with the blank problem in a data column that turns numbers into text because a least one blank is in the column,

Re-reproducing the calc fields in a less time consuming way.

Some of the questionnaires I would like to power pivot have 80+ questions!

Thanks in anticipation

John
Australia

 

[scottsen]

Can you paste some of your table(s) in here to help us visualize what's going on?

 

[MAYNAARD]

Thanks Scott

sorry I was not clear enough.

My work browser won't connect to this site, so no pics, but I will try to be clearer!

The data file in question contains columns of 1's or 0's or blanks, together with identifying data linked to Related files. Each data column is headed AQ1,AQ2 . . . Etc. 1 = Satisfied, 0= Unsatisfied, Blank = Not applicable.

Whereas Excel has no difficulty counting, summing and averaging this data both horizontally and vertically, because PowerPivot automatically identifies any column with blanks as a text column, I need some other way of summarising the data in PowerPivot.

To date I have succeeded, by creating calculated fields, as previously explained, by Counting 1's, Counting Rows, Counting Blanks, and then calculating percentage Satisfaction as, in effect, the (sum of 1's)/((sum of Rows)-(sum of blanks)).
But it is tedious writing 4 calculations for each question, and I am seeking advice re shortcuts to same end.

So far it seems to me that the shortcuts may lie in:
Some other approach, which I have not thought of, due to my newness to DAX;
A simpler way of reproducing the Calculated fields (possibly using something like Excels "Indirect" to lookup the QA-Id); or
Some other approach all-together?

thanks again

John

 

[scottsen]

PowerPivot automatically identifies any column with blanks as a text column

Hrm... I don't think that is true.

This is what your data looks like?

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl65, width: 64"]AQ1[/TD]
[TD="class: xl65, width: 64"]AQ2[/TD]
[TD="class: xl65, width: 64"]AQ3[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[MAYNAARD]

Dear Scott

My data looks exactly like that!

 

[scottsen]

So, =SUM(Table[AQ1]) should certainly work, regardless of blanks. AVERAGE may be more interesting with the blanks, depending on what you mean by "average" in that case.

The larger problem, imo, is you would kinda end up writing at least 1 measure per column. Any way you can "unpivot" this data, so that it's all in a single column (with a column that specifies the AQ#) ?

 

[MAYNAARD]

Thanks again Scott

So, =SUM(Table[AQ1]) should certainly work, regardless of blanks. AVERAGE may be more interesting with the blanks, depending on what you mean by "average" in that case.


I haven't tried =Average(Table[Aq1]), precisely because of the Blanks problem, although as indicated =Sum(Table[Aq1] does work. But as you note:

The larger problem, imo, is you would kinda end up writing at least 1 measure per column. Any way you can "unpivot" this data, so that it's all in a single column (with a column that specifies the AQ#) ?

Not sure about unPivoting: Is it like CopyPaste Soecial:Transpose in Excel?
Which is why I was wondering if there is an easy way of re-creating Calculated Rows once a model Query has been identified!

 

[scottsen]

I don't know if any built in ways to restructure your table. Other excel ninja's are probably better qualified there. I suspect PowerQuery could do this, though.

For the average it's probably easiest to do [TotalScore] / [NumScores] as explicit measures (TotalScore is of course the Sum, and NumScores is... whatever is most appropriate COUNTROWS, COUNT, COUNT+COUNTBLANK, whatevs.

It's not SUPER clear to me what your "end goal" is, I am assuming that if you can get your data in a better form, you would write stuff like:
AvgQuestion1 := CALCULATE([AverageScore], Questions[QuestionNumber] = 1)
?