topic Re: "NULL" rows affect the calculation Power BI in DAX Commands and Tips

"NULL" rows affect the calculation Power BI

Sidpug2013 — Fri, 25 Oct 2024 06:56:28 GMT

Hi,

I put below an example of my problem.

When I want to calculate Penetration Index on various levels, on a Power BI matrix, I get the calculations wrong.
On Power BI I replicated the calculation with Dax, for convenience.

When I make a matrix and put AggregateDepartment and CheckoutDepartment on the rows, in order, it gives me wrong percentage values because there are ‘NULL’ values.

For example:
If I put in an array visual object on the rows the AggregateDepartment and then put the Measure it gives me correct result (when I put filter level 1 or when CodDep is null).
If I put in the bottom level of the matrix, in the rows, the CheckoutDepartment, it gives me wrong results obviously because of the level 1 filter or CodDep = null).

The structure of the file looks like this Divided into 5 levels (AggregateDepartment, CheckoutDepartment, mer1, mer2, mer3). The Penetration Indexes refer to the level. After that begin those of CheckoutDepartment, etc.

I would like to have the right Penetration Indexes on all levels, corresponding to the right descriptions.

How can I solve this problem?

Re: "NULL" rows affect the calculation Power BI

BeaBF — Thu, 24 Oct 2024 10:10:50 GMT

Sidpug2013 Can you paste the current formula used?

BBF

Re: "NULL" rows affect the calculation Power BI

Sidpug2013 — Thu, 24 Oct 2024 10:44:36 GMT

BeaBFHi, the formula is: Column "O" / Column "P"

In PBI CountGroup is Column"O" in Excel and CountTotal is Column "P" .

Dax Measure is:
SumX('Table', 'Table'[CountGroup]) / SumX ('Table', 'Table'[CountTotal])

I do also the calculated column:

VAR REPAGG = 'Table'[CountGroup]

VAR TOT = 'Table'[CountTotal]

RETURN

DIVIDE(REPAGG, TOT)

Re: "NULL" rows affect the calculation Power BI

Anonymous — Fri, 25 Oct 2024 02:41:00 GMT

Hi Sidpug2013

If you're facing issues with NULL values or incorrect percentages in your matrix, consider using the DIVIDE function for better handling of division by zero:

Measure = DIVIDE(SUMX('Table', 'Table'[CountGroup]), SUMX('Table', 'Table'[CountTotal]), 0)

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: "NULL" rows affect the calculation Power BI

BeaBF — Fri, 25 Oct 2024 07:20:33 GMT

Sidpug2013 Try with:

PenetrationIndex :=
IF(
ISINSCOPE('Table'[CheckoutDepartment]),
DIVIDE(SUM('Table'[CountGroup]), SUM('Table'[CountTotal])),
IF(
ISINSCOPE('Table'[AggregateDepartment]),
DIVIDE(SUM('Table'[CountGroup]), SUM('Table'[CountTotal]), 0),
BLANK() -- Optionally, return BLANK for other levels or handle differently
)
)

BBF