Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Creating a new summary table with multiple binary columns

I have some survey data I want to aggregate as in the image below.   Essentially, I want a column with a sum of all customers, grouped by the question and the date. I have a table where all these q...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Anonymous,

     

    The suggestion of Ashish_Mathur is meaningful, but need a little change by my test.

     

    1. As Ashish_Mathur suggested, please unpivot the column Q1 to Q5.

    2. Create the measure with the formula below.

     

    Yes responses =
CALCULATE (
    COUNTROWS ( 'Data' ),
    ALLEXCEPT ( Data, 'Data'[Date] ),
    FILTER ( 'Data', 'Data'[Value] = 1 )
)

No responses =
CALCULATE (
    COUNTROWS ( 'Data' ),
    ALLEXCEPT ( Data, 'Data'[Date] ),
    FILTER ( 'Data', 'Data'[Value] = 0 )
)

    Here is the output.

     

     

     

    Best Regards,

    Cherry

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi Anonymous,

 

The suggestion of Ashish_Mathur is meaningful, but need a little change by my test.

 

1. As Ashish_Mathur suggested, please unpivot the column Q1 to Q5.

2. Create the measure with the formula below.

 

Yes responses =
CALCULATE (
    COUNTROWS ( 'Data' ),
    ALLEXCEPT ( Data, 'Data'[Date] ),
    FILTER ( 'Data', 'Data'[Value] = 1 )
)

No responses =
CALCULATE (
    COUNTROWS ( 'Data' ),
    ALLEXCEPT ( Data, 'Data'[Date] ),
    FILTER ( 'Data', 'Data'[Value] = 0 )
)

Here is the output.

 

 

 

Best Regards,

Cherry