PowerBI groupby and summarizing

0
votes

I have a dataframe like shown below in PowerBI:

Date             Store    Group    Value
01 April 2015    1000     Fish     98

How can I write a DAX code to aggregate the rows and group by Store, Group and Date (over year quaters) and sum up the Values. I would like to have the date column to be a yearquarter column in the format: 2014 Q1.

1
powerbidax
Have you looked into Summarize? – Rory

1 Answers

0
votes

This should do the trick:

First, create a 'Year Qtr' field by adding a calculated column to your table: 

YearQtr =
YEAR ( 'Table'[Date] ) & " "
    & CONCATENATE ( "Q", CEILING ( MONTH ( 'Table'[Date] ) / 3, 1 ) )

Then, you can create a calculated table using the following DAX: 

AggTable:=
SUMMARIZE (
    'Table',
    'Table'[Store],
    'Table'[Group],
    'Table'[YearQtr],
    "Sum", SUM ( 'Table'[Value] )
)

Hope it helps!