get good numbers in Product Table when select any field in SubProduct table

[Analitika]

Product visual table

Headers	| ProdName	| Average	| MIN	| MAX
Fields	| ProdNameCol	| Average_Of_mProd	|  MIN_Of_mProd	|  MAX_Of_mProd

SubProduct visual table

Headers	| SubProdName	| Average	| MIN	| MAX
Fields	| SubProdNameCol	| Average_Of_mSubProd	| MIN_Of_mSubProd	| MAX_Of_m_SubProd

In visual Product table i using this measure

mProd =

IF (

'Table'[Code] = 'Table'[Code2];

DIVIDE (

'Table'[Summ] ;

'Table'[Count]

)  )

in secont visual table SubProduct i use measure

mSubProd=

IF (

'Table'[Code] <> 'Table'[Code2];

DIVIDE (

'Table'[Summ] ;

'Table'[Count]

)  )

So when i select in SubProduct any field, i didnt see any Product tables elements

if i change Product Table measure to (adding else statment 😞

mProd=

IF (

'Table'[Code] = 'Table'[Code2];

DIVIDE (

'Table'[Summ] ;

'Table'[Count]

); 0  )

then Product Table shows elements but with wrong average, min and max

PBIX

How to get good numbers in Product Table when select any field in SubProduct table?

[v-lionel-msft]

Hi @Analitika ,

Can the [measureProd] measure return the correct value? Does it all return 0?

Please give a sample data and screenshots of your two visual tables.

Best regards,
Lionel Chen

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

[Analitika]

[measureProd]  return correct info while meet criteria 'Table'[Code] = 'Table'[Code2];

I cant post visuals as it is confidencial info

[v-lionel-msft]

Hi @Analitika ,

Has your problem been solved?

You have too little information to share, I cannot judge the error. Maybe you can refer to my .pbix file(Although I did not reproduce your problem.).

Best regards,
Lionel Chen

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

[Analitika]

TY. Look please for updated question. attached PBIX

[v-lionel-msft]

Hi @Analitika ,

As you can see, when you filter the [SubName] column, the [SumProd] column will return blank value. This is not a formula error, but your original data itself has such a correspondence.

In other words, it is correct to return a blank value.

Or you can assign the blank value to 0.

SumProd = 
VAR x =  
IF(
    'Table'[Code1]='Table'[Code2],
    DIVIDE('Table'[Price],'Table'[Count])
)
RETURN
IF(
    x = BLANK(),
    0,
    x
)

Best regards,
Lionel Chen

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

[Analitika]

I know that. So how to do what i need? As i need to see ProductName and his averages when press on SubName

[v-lionel-msft · Accepted solution]

Hi @Analitika ,

Maybe you can create a calculated column like this.

Sum__Prod = 
IF(
    [SumSubProd] <> BLANK(),
    [SumSubProd], 
    [SumProd]
)

Then add this calculated column to the matrix and calculate average, maximum and minimum respectively.

Best regards,
Lionel Chen

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