Monthly Archives: July 2021

Business Intelligence

DAX “related” function and SQL

A database consists of master data table with product information and a transaction table with sales volumes.

The master data table includes sales price, discount, and sales margin. The product is normally sold at sales price but sometimes discount is granted. Sales price minus costs equal to margin. The net sales equals to sales minus discount.

The data is stored in MySQL database.

Master data table md_prod.

Data is very minor but the idea is to demonstrate the logic.

I have in earlier blogs demonstrated how to report in Excel or in Power BI a data in MySQL database, therefore those parts are not included in this blog.

First we need to count the sales in money, that is sales volume multiplied by sales volume.

The DAX:

=SUMX(tr_sales,[sales]* RELATED(md_prod[price]))

This is also something I have presented in my earlier blogs.

The results.

Check the same from the database.

The results are similar in MySQL. Left join was used to take all the materials from left master data table. There are no sales records for the product P2, but as the P2 is in master data table, we will take that into our report.

To calculate the COGS we need to minus margin from sales price.

The DAX:

=SUMX(tr_sales,[sales]* (RELATED(md_prod[price] )))-SUMX(tr_sales,[sales]* (RELATED(md_prod[margin] )))

Then we check from MySQL.

The results are the same in BI and in SQL.

The DAX:

=SUMX(tr_sales,[sales]* ((RELATED(md_prod[price]))- (((RELATED(md_prod[price]))) *  (RELATED(md_prod[disc])))))

I almost got lost with parentheses in DAX.

When counting sales in money, COGS, or net sales, we took sales volume in the transaction table and material specific data in the master data table. In all three cases we used RELATED function.

However, I felt that SQL simpler than DAX is.

Functions

INDEX application

I have introduced INDEX earlier, here is one application how to use INDEX.

Sometimes it nice to calculate manually and check that Excel formula works correctly.

Let’s take matrix calculation with MMULT.

We have two arrays, one in A2:C3 and two in E2:F4.

Write the formula in A7 =MMULT(A2:C3;E2:F4) and press enter.

However, if you want to calculate manually.

The formula in A7 is =A2*E2+B2*E3+C2*E4, in B7 =A2*F2+B2*F3+C2*F4, in A8 =A3*E2+B3*E3+C3*E4 and in B8 =A3*F2+B3*F3+C3*F4.

You can automate the manual process.

Activate the range B2:F4 and rename the range by replacing A2 in the small window. I wrote Mtr as the name for the range.

I have earlier written a blog text about INDEX. You define the range, in this case it is Mrt B2:F4. Coordinates 1;1 refer to B2, coordinates 1;5 to E2.

Formulas in

A6: =INDEX(Mtr;1;1)*INDEX(Mtr;1;5)+INDEX(Mtr;1;2)*INDEX(Mtr;2;5)+INDEX(Mtr;1;3)*INDEX(Mtr;3;5)

B6: =INDEX(Mtr;1;1)*INDEX(Mtr;1;6)+INDEX(Mtr;1;2)*INDEX(Mtr;2;6)+INDEX(Mtr;1;3)*INDEX(Mtr;3;6)

A7: =INDEX(Mtr;2;1)*INDEX(Mtr;1;5)+INDEX(Mtr;2;2)*INDEX(Mtr;2;5)+INDEX(Mtr;2;3)*INDEX(Mtr;3;5)

B7: =INDEX(Mtr;2;1)*INDEX(Mtr;1;6)+INDEX(Mtr;2;2)*INDEX(Mtr;2;6)+INDEX(Mtr;2;3)*INDEX(Mtr;3;6)

An advantage with index with renamed range, the formula strings can be copied as long as range is named after Mtr.

In the picture above the range G2:L4 is named after Mtr.