The LED chart is not a chart, in the true sense of the word, but it looks nice and is a good choice to illustrate Variance analysis report. We make it so that, after analyzing the percentage for which the plan was exceeded or missed, with the help of the REPT function, generate a certain number of “squares” to illustrate the obtained result.
It is a common practice in domestic retail outlets to keep a large number of items under one code. In fact, it is one item, but its additional attributes are not taken into account, such as: color, size, design, season … Consequently, the stocks are incorrect. How to form item codes in such a way that, within the code itself, all its attributes are taken into account? We will use permutations!
Since the release of Excel 2007 it has been storing data as an XML file. This is a good practice if we want to exchange spreadsheets between different platforms, or even operating systems, but when it comes to complex calculations, which contain hundreds of thousands of rows, such documents can take up a lot of disk space. This can be overcome by saving with the use of the XSLB extension. You can read about the advantages and disadvantages of this approach in the text that follows.
Sometimes, when working on business reports, there is a need to create a formula that contains several criteria for searching a range. When we have only one criterion, it is enough to use a combination of INDEX and MATCH functions. When there are more of them, it is already a bit more complicated situation. However, this is not too complicated either, you will see in the “recipe” that follows…
Whether we use Excel BI or Power BI data model, we can create a hierarchy of employees manually or with the help of some DAX functions. In this text you will learn how to use two such functions: PATH and PATHITEM. With their help, it is possible to create calculated columns that show the hierarchical level of each of the employees within the company.