Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Analitika
Post Prodigy
Post Prodigy

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

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
2020-06-23_150219.png
2020-06-23_150102.png
 
 
 
 
How to get good numbers in Product Table when select any field in SubProduct table?
1 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.

q5.PNG

 

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.

View solution in original post

7 REPLIES 7
v-lionel-msft
Community Support
Community Support

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.

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

 

I cant post visuals as it is confidencial info

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.

TY. Look please for updated question. attached PBIX

Hi @Analitika ,

 

12b.PNG

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.

 

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

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.

q5.PNG

 

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.