REDUCE Your Measures With Calculation Groups In Power BI

-

 


You will need an external tool Tabular Editor for this. If you already don't have it, then please install it first.

You can visit the Tabular Editor website to download the latest installer.

Calculation groups are a new feature in DAX, inspired by a similar feature available in MDX known as calculated members. Calculation groups are easy to use; however, designing a model with calculation groups correctly can be challenging when you create multiple calculation groups or when you use calculation items in measures.

Let's see first why calculation groups are useful:


Imagine we have these measures:


Sales Amount   := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
Total Cost     := SUMX ( Sales, Sales[Quantity] * Sales[Unit Cost] )
Margin         := [Sales Amount] - [Total Cost]
Sales Quantity := SUM ( Sales[Quantity] )

And we want to calculate YTD, MTd ...etc for these measures:

YTD Sales Amount :=
CALCULATE (
    [Sales Amount],
    DATESYTD ( 'Date'[Date] )
)
 
YTD Total Cost :=
CALCULATE (
    [Total Cost],
    DATESYTD ( 'Date'[Date] )
)
 
YTD Margin :=
CALCULATE (
    [Margin],
    DATESYTD ( 'Date'[Date] )
)
 
YTD Sales Quantity :=
CALCULATE (
    [Sales Quantity],
    DATESYTD ( 'Date'[Date] )
)

Instead of creating all these measures, we can simply create a calculation item that takes a measure and calculate its YTD, and hence we can create a calculation group of all desired calculation items.

First: Launch tabular editor from your model 



Then Create a new calculation group



I named the group Time Intelligence and created a couple of 
calculation items







And it goes like this:

every calculation item takes the selected measure and applies the desired filter to it


-
-- Calculation Item: YTD
--
    CALCULATE (
        SELECTEDMEASURE (),
        DATESYTD ( 'Date'[Date] )
    )
 
--
-- Calculation Item: MTD
--
    CALCULATE (
        SELECTEDMEASURE (),
        DATESMTD ( 'Date'[Date] )
    )


So, I've many measures for calculating certain values, so basically after selecting all the measures
I dragged and dropped "Name" which is my calculation group in columns
so what you see here is MTD, YTD for all measures To the selected date 
in the slicer.


Also, You can use your calculation group in a slicer of course.

Be careful: calculation groups turn off your implicit measures and you will have to write all of them explicitly.


Conclusions

Calculation groups are a new and exciting feature of DAX. In this first article, we just introduced calculation groups. The following articles describe in more detail the properties available, several examples of possible usages, and the best practice to write reliable code.






Comments

Post a Comment

Popular posts from this blog

Get The Distinct Count Of Values By Each Day Using DAX

-
Image
  We have a serious issue when it comes to calculating the distinct count of values based on dates, why? cause if you want to get the distinct count for each day, the usual distinct count measure will ignore the day-by-day calculating and will get the distinct count for the whole period.  so let's see how to overcome this. The solution is simple ... we will create a summarization by date and we will iterate through it calculating the distinct count This is the original measure that calculates the distinct count This is our new adjusted measure it simply defines a variable that holds a summarization of our table by date  and through this summarization , we will iterate and calculate the sum of the distinct count for each day Here we can see that for one day the two measures give the same value and it's reasonable  This is the tricky part that the new measure overcomes, for more days, the old measure gets the unique values for the whole bunch of days, not for each day....
Read more

Integrating Python in Power BI With An Example Of Data Wrangling & Data Visualization

-
Image
  With a mix of Power BI & Excel's versatility and coding experience, You can easily wrangle any kind of data and get the most out of it. Let's see how we can use python in two processes (Data Wrangling & Data Visualization) inside Power BI Make sure that anaconda and python3 installed on your pc. Our Data set The dataset I'm using for this article is a Kaggle dataset that I've recently worked on you can check it out here what we are going to do is to transform some of my work in this notebook to power bi. 2. Data wrangling The data wrangling process in power bi is based inside the query editor After loading your data click on the transform tab then run a python script then you will need to import your libraries and run your code for me, the dataset only needed to replace some values and to get the date and time  for more information check out my Kaggle notebook You will get this after running your code this is the new dataset after running your code  so all you...
Read more

Tips To Overcome Over plotting of Dense Scatter Plots.

-
Image
Here are some tricks to overcome overplotting of dense scatter plots Use a transparency factor: # Matplotlib scatter plot with an alpha value plt . scatter ( df [ X_COL ], df [ Y_COL ], alpha = 0.03 ) plt . xlabel ( X_COL ) plt . ylabel ( Y_COL ) plt . show () This will introduce some transparency to your visual and the less transparency the denser. You can make this even cooler with animated transparency: # Initialize plot and animation camera fig , ( ax1 , ax2 ) = plt . subplots ( 1 , 2 ) # Create 2 camera = Camera ( fig ) # Create a sequence of alpha values alpha_range = np . linspace ( 0.5 , 0 , 30 ) ** 3 # For each alpha value for alpha_value in alpha_range : # Plot "vanilia" plot for reference ax1 . scatter ( df [ X_COL ], df [ Y_COL ], color = 'black' ) # Plot scatter plot with the alpha value ax2 . scatter ( df [ X_COL ], df [ Y_COL ], alpha = alpha_value , color = 'black' ) # Take a ...
Read more