To know more about SELECTEDMEASURE(), please refer to this link. SELECTEDMEASURE() is used by expressions for calculation items to reference the measure that is currently in context. Define the measure using DAX and the blueprint shown below. Now, right-click on the calculated group you just created and click on ‘ New Calculation Item’ and add items YTD, QTD, and MTD. You can rename the column as you like and it will be displayed on Power BI Desktop. It is a table that has a single column, by default it is named as Names in Tabular Editor. Here, it is named as Time Calculation.Ī Calculation Group can apply specific calculations on top of existing DAX measures. Name your Calculation Group as per your choice.Select Create New > Calculation Group or you can directly press Alt+7.In Tabular Editor, on the left hand side panel, right click on the Tables. This will open the Tabular Editor linked to the DataModel of the existing Power BI file. After setting up the Tabular Editor, click on the External Tool tab in Power BI Desktop (as shown below) and then click on the Tabular Editor icon.To cut-short the lengthier process, we would need to follow below steps: Time related measures for each of the 3 Base Measures = 3 * 3 = 9 So, the longer route will be – creation of measure for each of the cases as below.īase Measures Total Gross Sales = SUM(financials) Total Profit = SUM(financials) Total Sales = SUM(financials)Īnd the time-related measures will be as below: YTD Gross Sales = Calculate (, DATESYTD ( 'Date' )) YTD Profit = Calculate (, DATESYTD ( 'Date' )) YTD Sales = Calculate (, DATESYTD ( 'Date' ))Īnd the same would be for MTD and QTD as shown above, and we would end up creating 12 measures in total. Let’s take an example to see how it works:īelow are the measures that I have created and for each measure, I would create measures to calculate YTD, MTD and QTD. If you haven’t downloaded Tabular Editor yet, please click to download the latest version here. To work with the Calculation Group, one must have Tabular Editor installed on the machine. numbers for two specific regions/products/customers/etc. For example, we want to see most of our Sales, Profit, Margin, etc. Normally, we use a Calculation Group for time-related calculations but it could be used for other situations too. It will drastically reduce the number of measures in a model. For this, we would use the concept of ‘Calculation Groups’ that can be used with an external tool – Tabular Editor. So, here we are with the way on how to reduce the number of measures for scenarios like this. Well, nowadays where there is a lot of advancement in Power BI and the external tools used for it, it seems nothing is impossible. It just creates redundant measures and thus it sometimes becomes difficult to handle such models where we have such time-related calculations for most of the measures. Total Profit YTD = CALCULATE (,DATESYTD ( ‘Date' ) ) Total Profit QTD = CALCULATE (,DATESQTD ( ‘Date' ) ) Total Profit MTD = CALCULATE (,DATESMTD ( ‘Date' ) ) Total Sales YTD = CALCULATE (,DATESYTD ( ‘Date' ) ) Total Sales QTD = CALCULATE (,DATESQTD ( ‘Date' ) ) Total Sales MTD = CALCULATE (,DATESMTD ( ‘Date' ) ) Total Gross YTD = CALCULATE (,DATESYTD ( ‘Date' ) ) Total Gross QTD = CALCULATE (,DATESQTD ( ‘Date' ) ) Total Gross MTD = CALCULATE (,DATESMTD ( ‘Date' ) ) Calculation Groups in Tabular Editor Inkey, J2329 ViewsĬonsider a scenario where your measure’s definitions are the same but the only difference is the base measures.įor example, while doing time-related calculations like MTD, YTD, QTD for 3 different calculations/measures, we need to create 3 measures for MTD, 3 measures for YTD, and 3 measures for QTD, resulting in total 9 measures such as :
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |