Metrics by days in columns

[NOVICE02]

Hi All,

Posted this question before and got some direction but need more.

My transactional data looks like this. It's thousands of rows for each day with multiple variables. 

ID	Variable	Variable2	Date
xx	xx	xx	Jan 1
xx	x	xx	Jan 2

right now I can only have it with the date running down vertically and the variable summaries running down vertically as well.

I need to summarize data like below.

	Jan1	Jan2	Jan3
variable1	sum/count/measure
variable2	Sum/count/measure

I have tried showing the values in rows but realized later that the subcolumn totals are all wrong.... it doesn't make any sense. I need to keep it in direct query mode so i tried the following as a test to unpivot but this is not working either...

select
  cal_date,
  Calls_Handled
from
 OB_Portfolio4 unpivot (
    Volumes for Metrics IN (Calls_Handled AS 'Calls_Handled ')
  );

but keep getting this error:   Details: "Microsoft SQL: Incorrect syntax near the keyword 'AS'."

Any direction would be appreciated

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i create a table to test:

Please take following steps:

1)Unpivot the columns [Variable] and [Variable2] in table in Query Editor:

2)After Apply&Close, create a measure:

Measure = SUM('Table'[Value])

3)Choose a matrix visual and it shows the sum of [Variable] and [Variable2] for each day:

Hope this helps.

Best Regards,

Giotto Zhi

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i create a table to test:

Please take following steps:

1)Unpivot the columns [Variable] and [Variable2] in table in Query Editor:

2)After Apply&Close, create a measure:

Measure = SUM('Table'[Value])

3)Choose a matrix visual and it shows the sum of [Variable] and [Variable2] for each day:

Hope this helps.

Best Regards,

Giotto Zhi

[NOVICE02]

Hi Giotto,

Thanks so much but i cannot unpivot that way i am using direct query. it saying that i have to go into import mode. Is there another way?

[v-gizhi-msft · Accepted solution]

Hi,

Please try to enter a table like this:

 

Then create a calculated column in 'Table(2)':

Column = IF('Table (2)'[Attribute]="Variable",RELATED('Table'[Variable]),RELATED('Table'[Variable2]))

And it shows:

Then create a measure:

Measure = SUM('Table (2)'[Column])

Choose a matrix visual, it shows:

Hope this helps.

Best Regards,

Giotto Zhi