cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Support
Community Support

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

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
Highlighted
Community Support
Community Support

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

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.

Highlighted
Post Prodigy
Post Prodigy

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

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

 

I cant post visuals as it is confidencial info

Highlighted
Community Support
Community Support

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

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.

Highlighted
Post Prodigy
Post Prodigy

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

TY. Look please for updated question. attached PBIX

Highlighted
Community Support
Community Support

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

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.

 

Highlighted
Post Prodigy
Post Prodigy

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

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

Highlighted
Community Support
Community Support

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

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors