Tag: Power BI

Categories
Application Data Visualisation Power Apps Power BI

Power BI – Quick Measures

Microsoft has prepared Power BI to make it easier for users to perform DAX measures without a lot of DAX knowledge.

To access the Quick Measures, click QUICK MEASURES on the Home tab. You will see a list of options to choose from. On the right side of the Quick Measure dialog box, you will find all the tables from your data model.

DAX

Example 1 – Rolling Average

Many use rolling average to smoothing the data set. Unusual periods can be disrupting for understanding patterns and especially in projections, they can make forecasts unnecessarily inaccurate.

In the example below a line chart visualises sales numbers over several years, but a number of periods were unusual and you want the audience to understand how the quantity would look under normal conditions.

DAX

From the dialog box you can select the Quick Measure ‘Rolling Average’ from the Calculation list. You will now need to add the fields from your tables and set up the parameters.

In this example the quantity is the Base value, Dates are added to the Date field (Rolling Average is a Time Intelligent measure and the primary key from the timetable needs to be added), and Periods before and after are set to 3 months (the smoothing level).

Measures

When you click OK Power BI will the write the DAX.

Quantity rolling average  =

IF(

ISFILTERED(‘Dates'[Dates]),

ERROR(“Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.”),

VAR __LAST_DATE = ENDOFMONTH(‘Dates'[Dates].[Date])

VAR __DATE_PERIOD =

DATESBETWEEN(

‘Dates'[Dates].[Date],

STARTOFMONTH(DATEADD(__LAST_DATE, -3, MONTH)),

ENDOFMONTH(DATEADD(__LAST_DATE, 3, MONTH))

)

RETURN

AVERAGEX(

CALCULATETABLE(

SUMMARIZE(

VALUES(‘Dates’),

‘Dates'[Dates].[Year],

‘Dates'[Dates].[QuarterNo],

‘Dates'[Dates].[Quarter],

‘Dates'[Dates].[MonthNo],

‘Dates'[Dates].[Month]

),

__DATE_PERIOD

),

CALCULATE(SUM(‘Line_Items'[Quantity]), ALL(‘Dates'[Dates].[Day]))

)

)

 

By adding the Rolling Average to the line chart, you can see the result below.

Measures

Example 2 – Percentage difference from filtered value.

The line chart below (the columns) shows sales in 3 different countries Canada, Mexico, and United States. It shows how much is generated in sales by Canada and Mexico by percentage.

The line is calculated by another Quick Measure – Percentage difference from filtered value.

Measures

In this example the Base Value is a sales measure. You can define how you want this quick measure the handle blanks. You can display them as blanks or you can tell the measure to treat blanks as zero. In this example the measure is filtered by country, and we have selected United States.

Measures

And as in the first example Power BI will write the DAX.

sales % difference from U.S.A. =

VAR __BASELINE_VALUE = CALCULATE([sales], ‘Customers'[Country] IN { “U.S.A.” })

VAR __MEASURE_VALUE = [sales]

RETURN

IF(

NOT ISBLANK(__MEASURE_VALUE),

DIVIDE(__MEASURE_VALUE – __BASELINE_VALUE, __BASELINE_VALUE)

)

Example 3 – Correlation Coefficient

In this example correlation between product unit prices and sales quantity needs to be investigated. Does the price affect the quantity sold?

The quantity and unit price have been added to a scatter chard below. The trend line will indicate to us the relationship between the two. The dots are spread out. This indicates that there isn’t a close relationship between the two variables, but what is the correlation coefficient?

A Quick Measure can very simply find the result.

Category here is the products identified by the field Product ID. Measure X and Measure Y are the here the sum of quantity and sum of unit price.

Again, Power BI will write the DAX.

Sum of Unit Price and Quantity correlation for Product ID =

VAR __CORRELATION_TABLE = VALUES(‘Items'[Product ID])

VAR __COUNT =

COUNTX(

KEEPFILTERS(__CORRELATION_TABLE),

CALCULATE(SUM(‘Items'[Unit Price]) * SUM(‘Line_Items'[Quantity]))

)

VAR __SUM_X =

SUMX(

KEEPFILTERS(__CORRELATION_TABLE),

CALCULATE(SUM(‘Items'[Unit Price]))

)

VAR __SUM_Y =

SUMX(

KEEPFILTERS(__CORRELATION_TABLE),

CALCULATE(SUM(‘Line_Items'[Quantity]))

)

VAR __SUM_XY =

SUMX(

KEEPFILTERS(__CORRELATION_TABLE),

CALCULATE(SUM(‘Items'[Unit Price]) * SUM(‘Line_Items'[Quantity]) * 1.)

)

VAR __SUM_X2 =

SUMX(

KEEPFILTERS(__CORRELATION_TABLE),

CALCULATE(SUM(‘Items'[Unit Price]) ^ 2)

)

VAR __SUM_Y2 =

SUMX(

KEEPFILTERS(__CORRELATION_TABLE),

CALCULATE(SUM(‘Line_Items'[Quantity]) ^ 2)

)

RETURN

DIVIDE(

__COUNT * __SUM_XY – __SUM_X * __SUM_Y * 1.,

SQRT(

(__COUNT * __SUM_X2 – __SUM_X ^ 2)

* (__COUNT * __SUM_Y2 – __SUM_Y ^ 2)

)

)

 

The correlation between unit price and quantity here is -0.15. In other words when a product gets more expensive the sold quantity decreases. But a negative correlation of -0.15 isn’t much. Nevertheless it could be a clever idea to keep an eye on this number over time to understand the sales pattern.

Conclusion

Microsoft has given their clients a shortcut to create DAX measures by offer the quick measure tool. Quite complicated measures can be achieved without prior DAX knowledge.

Categories
Hints & Tips Power Apps Power BI Power Platform

Power BI’s Mysterious Calculate Function 2: Filter Context

DAX (Data Analysis eXpressions) is the function language in Power BI desktop. When DAX is used to create measures, it can hard to understand the logic sometimes. Especially one function, the Calculate function, can be challenging.

calculate

 

This is the second part of a series of blog posts, which will investigate the mysterious Calculate function.

It reacts differently to row, column, filter, and table context than the other DAX functions, and will be needed for nesting a number of DAX functions.

In this blog post you will see some examples of how the calculate function works in filter context.

Matrix visual

In the example below, you can see sales in a matrix visual. The visual displays total sales for each sales rep, and sales for a specific product. In this example, backpacks are the product.

In the example the sales have been filtered and calculated by using the SUMX function.

Sales of Backpack = sumx(filter(Line_Items,RELATED(Items[Product Description])=”backpack”),[sales])

 

To do it with the SUMX function, you need to use the FILTER function. We need a Related function to reference the column in the Items table, and the expression is the measured total sales.

 

calculate

 

Now Using the Calculate Function

Now let us see how to do the same, but this time with the mysterious Calculate function.

Calc Sales Backpack = CALCULATE([sales],Items[Product Description]=”backpack”)

 

You can now do it with only one function – the Calculate function. This means that you will not need to use the Filter function to filter by the product, and you will no longer need to use the Related function.

See the matrix below. The Calculate measure returns exactly the same as the SUMX measure.

calculate

But the mysterious Calculate function can behave may be a way which seems illogical.

Below the matrix has been amended to display sales by product instead of sales rep.

calculate

 

Now the Calculate measure, as you can see above, cannot understand that it needs to filter the table by each product. The SUMX measure can. The Calculate measure just shows the same sales of backpack total for all the products, but it does not sum up all the values in the Total row at the bottom of the matrix.

To get the Calculate function to return the right result, a Filter and a Related function are needed.

Calc Sales Backpack = CALCULATE([sales],filter(Line_Items,RELATED(Items[Product Description])=”backpack”))

 

Above you can see that the amendment of the Calculate function now shows the expected result.

But can this special behaviour be useful?

You could be in a situation, where you want to compare the sales of backpack up against the other products. Following on, the next example shows a percentage difference of backpack sales versus other products which needs to be visualised.

Backpack % of other products = DIVIDE([sales],CALCULATE([sales],Items[Product Description]=”backpack”),0)

 

The measure above will do the job. The Calculate measure without the Filter function or the Related function will return the total sales of backpack in each other product’s rows in the matrix. So that sales for each product will be measured against the sales of backpacks.

Above you can see the result of this approach, and of course the percentage difference from backpack and backpack will return 100%.

Conclusion

I call the Calculate function the mother of all DAX functions. It is the most important DAX function (my personal opinion), but to get the most out of it you will need to understand, how the function reacts to row, column, filter, and table context.

In the next blog post in this series, you will see how the mysterious function is needed for use of date intelligence in Power BI desktop measures.