Sample queries for KPI in Datazen

When I started working with Datazen I could not find to many samples out there. So to help people getting started I will provide some 🙂

The first DAX query is for creating a KPI in Datazen.

EVALUATE
(
   ROW("Internet total sales", [Internet Total Sales])
)

And with some filtering it might look like something like this

EVALUATE
(
	ROW("Internet total sales", CALCULATE([Internet Total Sales], 
		FILTER('Date', 'Date'[Calendar Year] = 2008)
		)
	)
)

To make a query for trend you will have to use nested summarize. The reason why you have to do this is that you need to have the measure as first column.

A single summarize will produce this result

EVALUATE
(
	SUMMARIZE
	(
		'Date',
		'Date'[Month],
		"Internet Total Sales", 
		CALCULATE([Internet Total Sales], 
			FILTER('Date', 'Date'[Calendar Year] = 2007)
		)
	)
)

The first column is month number and the next column is the measure.

Summarize datazen 1

But we will have to change it so that the measure is the first column.

EVALUATE
(
	SUMMARIZE
	(
		SUMMARIZE
		(
			'Date',
			'Date'[Month],
			"Internet Total Sales", 
			CALCULATE([Internet Total Sales], 
				FILTER('Date', 'Date'[Calendar Year] = 2007)
			)
		),
		[Internet Total Sales],
		'Date'[Month]
	) 
) ORDER BY 'Date'[Month] DESC

And the result

Summarize datazen 2

Advertisements