Building Calculation Groups in one click
Calculation groups are great for extracting logic in one central place instead of placing it on each measure. This abstraction means that you might even want to use the same calculation group on different data models, but then you might need to change some parts of the Calculation Items. Tabular Editor C# Scripts are a great way to make this process a breeze and give you super-powers! In this session we'll see some simple calculation groups and the process to create a macro that builds it with some customization on run-time of the script. Speaker Bio: Bernat Agullรณ is a data engineer and Power BI enthusiast based in Barcelona. He is a partner at Esbrina, where he writes his blog www.esbrina-ba.com/blog He's been recently awarded the MVP Data Platform award for his contributions to the community. ๐๐๐ง๐จ๐๐๐ฃ ๐๐ฝ๐๐๐- ๐๐๐๐ง๐ค๐จ๐ค๐๐ฉ ๐พ๐ค๐ข๐ข๐ช๐ฃ๐๐ฉ๐ฎ: https://bit.ly/3IAg7xT ๐๐๐ฃ๐ ๐๐๐๐ฃ: https://bit.ly/32tGkif ๐๐๐ก๐๐๐ง๐๐ข ๐พ๐๐๐ฃ๐ฃ๐๐ก: https://t.me/PersianPBIUG ๐๐ค๐ช๐๐ช๐๐: https://bit.ly/3hk20RL Language: EnglishOklahoma Power BI and Fabric User Group Feb 2024 Event w Zoe Douglas!
Zoe Douglas will present on - Calculation groups - Copilot in Power BI - More in-depth DAX Query View - And AZA (ask Zoe anything!) https://teams.microsoft.com/l/meetup-join/19%3ameeting_YjBmYjg5NjEtYWJiYy00MjIxLTkzNTktMjdiZmQ2NmJiNjBm%40thread.v2/0?context=%7b%22Tid%22%3a%22b4c9f32e-da17-4ded-9c95-ce9da38f25d9%22%2c%22Oid%22%3a%2274309470-c132-4e77-85c0-97ae4fd657e7%22%7d4to encuentro presencial
Nos reencontramos para compartir, aprender, charlar y disfrutar de un encuentro de comunidad. En este espacios vamos a intercambiar experiencias y escuchar charlas. En esta oportunidad aprenderemos de: 1. Licenciamiento en Fabric 2. Calculation groups en Power Bi 3. Custom Visuals en Power Bi Espero que los temas sean de su interรฉs y recuerden que esto es una comunidad. Compartimos nuestras experiencias. Si te gustarรญa aprender de un tema o te gustarรญa compartir un tema que estuviste estudiando, siempre habrรก lugar, basta con pedirle en el encuentro
Dynamic P&L fiscal year time intelligence for actual vs forecast vs budget vs same period last year
Hi Community, I am trying to create a dynamic time intelligent profit and loss statement comparing actuals to forecast, budget, and the same period last year for each line item. I am using a fiscal year calendar which means the standard time intelligence measures will not work. The comparison amount (column) needs to be the variance between the two and not the amount. For example, net sales, the actual amount is 10,000 and the forecast amount is 12,000. The P&L matrix/table should show 10,000 for the actual amount and (2,000) for the forecast amount. Below is a quick picture of what I am trying to achieve: Line-Item Actual vs Forecast vs Budget vs SPLY Net Sales 10,000 (2,000) (1,000) (3,000) COGS 4,000 2,000 1,000 500 Additionally, I would like to see this on a WTD, MTD, QTD, and YTD basis in one matrix/table. Below is a screenshot of the final product I am trying to create along with the format strings: As you can see, we have four different format strings: whole numbers, whole number dollars, decimal, and percentages. My data model is pictured below: Below is a picture of what I currently have: I have created all the individual line-item measures for actuals, forecast, and budget using the measure branching technique. However, I know this is not the best way to do this because I have three measures for each line item. For example, I have a measure for actual net sales, forecast net sales, and budgeted net sales. As you can see, the forecast, budget, and SPLY measures are not variance amounts. The actual, forecast and budget measures use the SWITCH technique which prevents me from calculating the variance because of the four different format strings and a custom time intelligence function. Below is an example of my Actuals measure: Act HL = SWITCH( [Selected Account], 1, FORMAT([Volume], "#,#;(#,#);-"), 2, FORMAT([Net Sales], "$#,#;($#,#);-"), 3, FORMAT([COGS], "$#,#;($#,#);-"), 4, FORMAT([Gross Margin], "$#,#;($#,#);-"), 5, FORMAT([MAP/MDF], "$#,#;($#,#);-"), 6, FORMAT([SG&A], "$#,#;($#,#);-"), 7, FORMAT([Combined EBIT], "$#,#;($#,#);-"), 9, FORMAT([Net Sales Rate], "$#,0.00;($#,0.00);-"), 10, FORMAT([COGS Rate], "$#,0.00;($#,0.00);-"), 11, FORMAT([Gross Margin Rate], "$#,0.00;($#,0.00);-"), 12, FORMAT([MAP/MDF Rate], "$#,0.00;($#,0.00);-"), 13, FORMAT([SG&A Rate], "$#,0.00;($#,0.00);-"), 14, FORMAT([Combined EBIT Rate], "$#,0.00;($#,0.00);-"), 16, FORMAT([GM as % of Net Sales], "0.0%;-0.0%;-"), 17, FORMAT([Combined EBIT as % of Net Sales], "0.0%;-0.0%;-") ) This measure above is then placed into a time intelligence pattern using the SQLBI DAX Patterns for Week-related calculations. As previously mentioned, I am using a fiscal year calendar so the standard time intelligence measures will not work. For example, the Act WTD column in my matrix above is: Act WTD = IF ( [ShowValueForDates], VAR LastDayOfWeekAvailable = MAX ( 'Date'[Day of Week Number] ) VAR LastFiscalYearWeekAvailable = MAX ( 'Date'[Fiscal Year Week Number] ) VAR Result = CALCULATE ( [Act HL], ALLEXCEPT ( 'Date', 'Date'[Working Day], 'Date'[Day of Week] ), 'Date'[Day of Week Number] <= LastDayOfWeekAvailable, 'Date'[Fiscal Year Week Number] = LastFiscalYearWeekAvailable ) RETURN Result ) The matrix has 16 measures like you see above (actual + forecast + budget + SPLY = 4 x 4 = 16 measures) Obviously this not an efficent or sustainable way to create this. I have a feeling Calculation Groups might help solve the problem. Additionally, when slicers are placed on the page, the query is slow because of the number of measures and the size of the data tables. So community, how can I achieve the above? Thank you for taking the time to read through my question.Conditional Formatting with Calculation Groups - Issues, please help me
Hi community, I developed a model with some conditional formatting, and I'm quite happy with it. Since the number of measures is quickly growing, I've started to implement Calculation Groups, to make it more efficient, and the model actually works fine with calculation groups implemented. Despite that, I found some issues with a simple conditional formatting configuration. Download - Sample Model (300 kb): https://www.dropbox.com/scl/fi/4sju5841r4m3afq7tewdo/Help_CalcGroups.zip?rlkey=v65nx5eshluxkz310ta7jacxe&dl=0 My basic conditional formatted visual without calculation groups looks as follows, and works just fine. The formatting condition is pretty simple and based on one field. The background of the rows of the table 'PL_Structure' is light blue where the field 'PL_Structure' [PL_Detail] has value 0 (running total rows), while are colored in light grey where the field value is 2 (subtotal row). The other - common rows - stay white (default color). The structure of the table can be found in the attachment together with the model (DataSource.xlsx), and includes an explanation of key fields as well. My formatting conditions The "PL SubTot Color" measure simply reads the SELECTEDVALUE of the above mentioned [PL_Detail] field): Now, with calculation groups implemented, I'm experimenting a strange behaviour, the numbers are just ok, but the table looks as follows: Maybe I did not fully understand the logics behind calculation groups, but I find them extremely useful for maintenance purposes. The attached model includes a side by side (with / without calculation groups). I do appreciate any suggestion or fix. Thank you. MarcoHow do I detect the active calculation item of a calculation group for a measure?
Suppose in this example I have a calculation group for cumulative sum I would like to have a measure that can detect what the active calculation item is. I notice that the Calculation Group can be accessed like a table in DAX. I create a measure to concatenate the measure names: Detect CalcGroup = CONCATENATEX('CalcGroup Cumulative Sum', CONVERT( [Cumulative Sum Group], STRING), ",") The card measure shows that it is working when both are not filtered out. However, when only one is filtered, an error is shown: I do not know why this is happening. As you can see in the DAX measure, I have purposely converted the [Cumulative Sum Group] column into a String just to see if this fixes the problem, but it does not. Also, it works fine for multiple values, but not for a single value. I was hoping to use this mechanism to detect which calculation group is active, is there any possibility of this working?
Calculation groups possibility to improve performance
Hi Champs, My dashboard report page has two overview boxes (one for the selected year and another for prior year) for different category values (such as Sales, Revenue, Profit, and Margin). Right now, we are using two measures for calculating each of the category values as mentioned below: Sales = sum(Data[sales]) Revenue = sum(Data[revenue]) Profit = sum(Data[profit]) Selected year = Year[Year] //Disconnected table to "Data" table Selected year sales = Calculate(Sales,Data[Year] = Selected year) Prior year sales = Calculate(Sales,Data[Year] = Selected year - 1) Selected year revenue = Calculate(Revenue,Data[Year] = Selected year) Prior year revenue = Calculate(Revenue,Data[Year] = Selected year - 1) Selected year Profit = Calculate(Profit,Data[Year] = Selected year) Prior year Profit = Calculate(Profit,Data[Year] = Selected year - 1) Now, the request is to reduce the number of measures. As the number of categories are increasing, would using calculation groups be a better option to improve performance? If yes, please can someone point in the direction to implement the calculation group. Thanks, AnthonyJosephCalculation Groups Very Slow
I am working on a report that uses calculation groups to let users select which time intelligence groups they would like to see (CY YTD, PY PTD, YTD Variance, CY TTM, PY TTM, TTM Variance). There is also a perspective set up to allow them to pull in certain dimensions from the dataset. This is all set up in Tabular Editor opened from the dataset. The KPIs are DAX measures within the Power BI Report itself. The dataset is a star schema and has several million records. Does anyone have any advice for how to optimize and speed up calculation groups within Power BI? Could it be related to my DAX measures being in the report instead of the DS? Any advice or things to try would be helpful. Thanks!Subtracting two measures not working
Hi Community, I am having an issue with what should be a simple thing to do, calculating the difference between measures. Below is the table: The "Account" field in rows is a calculated item from Calculation Group 1 and the "Time Calculation" is a calculated item from Calculation Group 2. As you can see, the difference between the whole number values is calculating correctly but the difference between the decimal numbers and percentages is not calculating correctly. (Highlighted in yellow) The three variances are calculated as follows: vs QBR = [Act] - [QBR] vs AOP = [Act] - [AOP] vs LY = [Act] - LY] The decimal numbers are taking "Account" divided "Volume". For example, Net Sales in Calculation Group 1 (row 10 in the matrix) Net Sales = VAR NS = CALCULATE ( SELECTEDMEASURE (), 'ACT/QBR/AOP'[Line Item] = "Net Sales" ) VAR Volume = CALCULATE ( SELECTEDMEASURE (), 'ACT/QBR/AOP'[Line Item] = "Volume" ) RETURN DIVIDE ( NS, Volume, 0 ) All other decimal numbers are calculated the same way except for the first variable. I am not sure what the issue is but hoping someone can assist with what should be an easy thing to do. Thank you!
