Select Page

Introduction

As someone that strictly uses Tableau, wrapping my head around Power BI was quite the struggle.  After our first few training sessions on Power BI I can say I am getting there but I still have a lot to learn.  A lot is the same just with different labels, a lot is also extremely different.  One thing that is unlike anything else is their coding language, DAX.  While the simple things make sense, one thing I struggled with was context.  I am writing this blog on a brief summary of context using DAX to not only help you but also help myself understand it in greater detail.  To compare to Tableau, these context calculations remind me of a Fixed LOD function.  To start I will be getting the total COVID cases for regions in Brazil and drilling down from there.

Grand Total

First of all I will create a table with the regions and sum of cases to show the amount of cases per region. This is done by dragging the variables into the ‘Columns’ section for the chart.

Next I created a measure that captures the grand total at the bottom of the table.  This is the total, fixed on the entire dataset, it is not affected by filters.  To do this I use the DAX code:

Grand Total Cases = CALCULATE(SUM(brazil_covid19[cases]), ALL(brazil_covid19))

As we can see when we add that to the table it shows the grand total for all regions, even when states are added the total remains the same.

Grand Total (All Selected)

Another version of this total would be to show the total of all regions on the chart.  We could do this if we wanted a dynamic percentage of total comparing to what is selected in the filter.  To do this we will need to use an ALLSELECTED function set on the table.  This means it will take all of the selected values in the table, as per any filters or slicers.  The code for this would be:

Grand Total (All Selected) = CALCULATE(SUM(brazil_covid19[cases]), ALLSELECTED(brazil_covid19))

With this added to the table, and some regions filtered out we can see the total of the selected regions.   In the example below the total created above does not change however this new Grand Total is dynamic.  This now gives us two options for totals and potential comparisons.

Percent of Total (All Rows)

Now we can use these different context totals to get percentage of Totals.  Starting with the percent of all the data, all rows.  This variable will use the ‘Grand Total Cases’ measure we created meaning it will remain constant, no matter what we have selected in the filter.  To do this we will use the following formula:

CALCULATE(SUM(brazil_covid19[cases])/ [Grand Total Cases])

This gives us the first row, divided by the third row.  We can then change the formatting to a percentage.  Adding this to the table we can see that when all regions are selected, the total percentage will add up to 100%.

As the denominator is locked on the entire dataset, when we remove Norte and Sul from the regions the total percentage is not 100% as it is not showing 100% of all the rows.  The calculation still breaks down into state values when they are added however the total is still based off of the entire dataset as seen below.  This is useful to see how all variations of variables make up the total percent of cases in Brazil.

Percent of Total (All Selected)

Now if we were to do the same calculation but use our Grand Total (All Selected) measure it will give us the percent of the total cases for the measures displayed on the table.  These measures can be chosen by the filter and we will keep the same regions selected so that we can see the difference.  This calculation will be:

CALCULATE(SUM(brazil_covid19[cases])/ [Grand Total (All Selected)])

As we can see below, the total for this measure is 100% and this will be the case for whatever is on the table.  It is taking the All Selected total so the percent of that total will always add up to 100%.

Conclusion

This was a basic introduction to context and ‘All’ functions using DAX.  There is so much that can be done with DAX and understanding this will help get to a deeper level of understanding that is needed to truly master the language.  I still have a long way to go but even just writing this blog has helped me understand context more. Thanks for reding this far.