Forum Discussion

PowerUser123's avatar
PowerUser123
Icon for Helper II rankHelper II
5 years ago
Solved

Matrix - Show Max as Values but Sum as Total

I have a matrix that looks like this based on my below measure:

 

 

Test = if(HASONEVALUE('Product'[ProductName]),MAX('Product'[ProductPrice]),SUM('Product'[Product Price]))

 

My measure works correctly for the row totals going across. It shows the sum. However, I would also like to show the sum of the columns going down but it currently just shows the max value. Is there a way I can alter the above to show sums as the total and max as values?

 

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi PowerUser123 ,

     

    Use the following measure, it will work as you expected:

     

    measurename =
SUMX (
    SUMMARIZE (
        'Product',
        'Product'[ProductName],
        "_MAXPRICE", MAX ( 'Product'[Product Price] )
    ),
    [_MAXPRICE]
)

     

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

     

    Best Regards,

    Dedmon Dai

3 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi PowerUser123 ,

     

    Use the following measure, it will work as you expected:

     

    measurename =
SUMX (
    SUMMARIZE (
        'Product',
        'Product'[ProductName],
        "_MAXPRICE", MAX ( 'Product'[Product Price] )
    ),
    [_MAXPRICE]
)

     

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

     

    Best Regards,

    Dedmon Dai

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    5 years ago

    Hey PowerUser123 ,

    use this DAX to create your measure:

    measurename = 
SUMX(
    VALUES('Product'[ProductName])
    ,CALCULATE(MAX(Product'[Product Price]))
)

    Hopefully, this provides what you are looking for.

     

    Regards,
    Tom

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, PowerUser123 

     

     

    I assume x y z are category.

     

    Please try the below.

     

    Test fix =
    IF (
    HASONEVALUE('Product'[ProductName]) && HASONEVALUE('Product'[Category]),
    MAX ( 'Product'[ProductPrice] ),
    SUM ( 'Product'[ProductPrice] )
    )
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM