Forum Discussion

bkxn226's avatar
bkxn226
Helper I
5 years ago
Solved

Matrix has different behavior than a pivot table for multiple columns

I have been trying to figure out how to do a quick pivot table similar to what would happen in Excel.   The matrix in Power BI really confuses me and I can't figure out its behavior if you have multiple columns.   

 

  • For instance, I want to be able to track pricing with different attributes by date, with date being the row.  I made my measure be a simple average of price. 
  • In order to show the model, I put the basic table on the left and two matrixes on the right. 

 

 

  • Can someone explain to me how it gets two $45 for the first matrix?  The second value (Subtotal -> no add on -> Tennis Racket,  with typical pivot table behavior, should clearly be $30.  How does it come out to $45? 
  • If you look at the second matrix, where is that 37.5 coming from.   It looks like one column leaks into another?  

Any help would be greatly appreciated.

 

 

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    Hi bkxn226 ,

     

    According to your question, look at the relationship model between the original tables, which may have an impact in the hierarchical structure. I did the test and got the correct expected result. The reference is as follows:

     

    M =
IF (
    HASONEVALUE ( test[price] ),
    MAX ( test[price] ),
    CALCULATE ( AVERAGE ( test[price] ), ALL ( test ) )
)

     

     

    Best Regards,
    Henry

     

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

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi bkxn226 ,

     

    How is your model setup? Do you have table relationships with filtering set for both sides? Are you using a measure or a value directly from a table? (is the value add on and no add on measures)

     

    Just picking up the information has you have it the only way to get those values would be to use the average or max values and don't do the drill down to the bottom of the matrix but that would not give the look and feel you are showing.

     

    Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    Hi bkxn226 ,

     

    According to your question, look at the relationship model between the original tables, which may have an impact in the hierarchical structure. I did the test and got the correct expected result. The reference is as follows:

     

    M =
IF (
    HASONEVALUE ( test[price] ),
    MAX ( test[price] ),
    CALCULATE ( AVERAGE ( test[price] ), ALL ( test ) )
)

     

     

    Best Regards,
    Henry

     

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