"NULL" rows affect the calculation Power BI

[Sidpug2013]

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?

[Sidpug2013]

@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)

[BeaBF]

@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

[Anonymous]

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.

[BeaBF]

@Sidpug2013 Can you paste the current formula used?

BBF