Forum Discussion

jayala's avatar
jayala
Frequent Visitor
6 years ago
Solved

Multiple values in the same cell (value and %)

Hello guys,

 

I am wondering if is there is a way to show multiple values in the same cell using a Matrix.

I have price and discounts as two separated rows and I also want to show the percentage of that discount over the price in the same cell inside brackets.

 

 

Do you know any way to do it?

 

Thank you in advance.

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi jayala ,

     

    Please create a measure as below.

    Measure = 
VAR va =
    SUM ( 'Table (2)'[value] )
VAR p =
    MAX ( 'Table (2)'[p/d] )
VAR pva =
    CALCULATE (
        SUM ( 'Table (2)'[value] ),
        FILTER (
            ALLEXCEPT ( 'Table (2)', 'Table (2)'[Month] ),
            'Table (2)'[p/d] = "Price"
        )
    )
RETURN
    IF (
        p = "discounts",
        va & "("
            & FORMAT ( ROUND ( va / pva, 2 ), "Percent" ) & ")",
        va
    )

    Pbix as attached.

     

3 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi jayala ,

     

    you could create a Text Measure.

    Discount Label =
IF (
    ISBLANK ( [Discount] ),
    BLANK (),
    FORMAT ( [Discount], "0" ) & " ("
        & FORMAT ( [Discount %], "0%" ) & ")"
)

    Regards,

    Marcus

    Dortmund - Germany
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support.

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi jayala ,

     

    Please create a measure as below.

    Measure = 
VAR va =
    SUM ( 'Table (2)'[value] )
VAR p =
    MAX ( 'Table (2)'[p/d] )
VAR pva =
    CALCULATE (
        SUM ( 'Table (2)'[value] ),
        FILTER (
            ALLEXCEPT ( 'Table (2)', 'Table (2)'[Month] ),
            'Table (2)'[p/d] = "Price"
        )
    )
RETURN
    IF (
        p = "discounts",
        va & "("
            & FORMAT ( ROUND ( va / pva, 2 ), "Percent" ) & ")",
        va
    )

    Pbix as attached.

     

    • jayala's avatar
      jayala
      Frequent Visitor
      6 years ago

      Hi v-frfei-msft thank you for your help.

       

      It looks pretty good. I will have to think my dataset structure again to implement it.