Skip to content

ilyar126

Power BI – split budget into ratios via DAX

  • ilyar126 

In certain times, you would like to split a higher grain number into lower grain by using some percentage ratios. For example, if you have a budget for Product Brand all up, we want to split further the budget into Product Names based on last year’s sales percentage ratios. You can do this by: Procedure steps to split budget categories First, we have the high-level budget figures for the next… Read More »Power BI – split budget into ratios via DAX

Excel Cube functions explained: Cubevalue, Cubeset, Cubemember

One of my favorite Excel functions to do business modeling are Cube functions. They act as a more efficient replacement for SUMIFS, GETPIVOTDATA functions that serve to sum aggregated results with various filters applied. Cube functions (CUBEVALUE, CUBEMEMBER, CUBESET) will only work if you have a PowerPivot model or live cube connection in your Excel file. How to use Cube functions In my experience, when using Cube functions, I prefer… Read More »Excel Cube functions explained: Cubevalue, Cubeset, Cubemember

Sum of all numbers from 1 to n

You all had this experience at least once. You have to calculate the sum of all numbers to n, so naturally you start counting and counting, starting all over again because you made a mistake and finally using a calculator to get the answer. Well I don’t want to ruin your day but you did everything wrong. There’s a much simpler way to do this. Here’s how: Formula to calculate… Read More »Sum of all numbers from 1 to n

Power BI: add KPI arrow triangles to your Variance Metrics

Anybody up for coloring Egyptian pyramids with Power BI? Just kidding 🙂 How about creating nice looking triangle arrows for your comparison measures colored dynamically based on the values. My goal is to assign up and down arrows or rectangles next to the year-over-year (yoy) growth percentages, colored green for going up and red for going down. The measure next to the percentage arrows should look something like that: Follow… Read More »Power BI: add KPI arrow triangles to your Variance Metrics

Excel Function – INDIRECT()

  • ilyar126 

Description Indirect function extracts the value of the referenced cell. Syntax INDIRECT(ref_text, [a1]) ref_text: cell address such as A1 or R1C1 format [a1]: if blank then assumes TRUE(), using standard cell reference. If FALSE() then assumes R1C1 format Examples Below are few examples how to use INDIRECT. Note that cell references have to be written in double quotations “” You can also use a combination of ADDRESS function to pass… Read More »Excel Function – INDIRECT()

Excel Function – ADDRESS()

  • ilyar126 

Description To calculate the address of a cell in an worksheet. You can use this function to refer cells in other worksheets as well. Syntax ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) row_num: specifies the row number col_num: specifies the column number [abs_num]: optional, which row or column to be fixed (absolute) with $ or relative [a1]: optional, it specifies A1 or R1C1 reference style if set to False or left blank.… Read More »Excel Function – ADDRESS()

Excel Function – ACOTH()

  • ilyar126 

Description To calculate the hyperbolic arctangent of a hyperbolic cotangent(number). The Hyperbolic Arctangent is an inverse of a hyperbolic cotangent is a number greater or equal to 1. Syntax ACOTH(number) Examples The results are as follows: References How to calculate Arctangent or ACOT(). If you want to see details what Hyperbolic Tangent is, check here.