Forum Discussion

sureshg2498's avatar
sureshg2498
Advocate II
1 year ago
Solved

Remove Aggregation from Matrix visual/Move cell value to front

Hi All,

 

I have created a Matrix visual with customer name under value. Since it aggreagtes i have taken Index to display all customers as shown below.

Current Matrix visual

 

 

Client Expected one:

Would it be possible to display the results in Matrix visual as shown above. Could anyone please help me on it?

Please find the below data set for matrix visual.

 

CountryFiscal QuarterCustomer Name
USAFY Q1 2024T Mobile (4/26/2023)
USAFY Q1 2024J&J (4/20/2023)
USAFY Q2 2024Wallmart (5/25/2023)

Desktopusers Anonymous  Ritaf1983  rajendraongole1  Bibiano_Geraldo  danextian 

Thanks,

Suresh.

 

Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    hI sureshg2498 ,

    Please, create a new calculated column by this measure:

    Custom Index = 
VAR CurrentCountry = 'Table'[Country]
VAR CurrentQuarter = 'Table'[Fiscal Quarter]
VAR CurrentIndex = 'Table'[Index]
VAR SortedTable =
    FILTER (
        'Table',
        'Table'[Country] = CurrentCountry && 
        'Table'[Fiscal Quarter] = CurrentQuarter
    )
RETURN
    RANKX(SortedTable, 'Table'[Index], , ASC, Dense)

     

    Now, instead to use index in your matrix, please, use Custom index column, and your final result should look like this:

     

     



6 Replies

  • dharmendars007's avatar
    dharmendars007
    Memorable Member
    1 year ago

    Hello sureshg2498 , 

     

    Create a calculated column that concatenates Customer Name with its respective date (e.g., "T Mobile (4/26/2023)"). This ensures each customer entry is unique.

     

    Customer with Date =
    'Table'[Customer Name] & " (" & FORMAT('Table'[Date], "MM/dd/yyyy") & ")"

    Use the new calculated column (Customer with Date) as the value, and change the aggregation to Do not summarize in the Values section.

     

    If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

     

    Thank You

    Dharmendar S

    LinkedIN 

    • sureshg2498's avatar
      sureshg2498
      Advocate II
      1 year ago

      Thank you so much for your reply Dharmendar.

      DB level we have concatinated customer name with start date. However I could not see the Do not Summarize under Values section. Could you please check and help me on it.

       

       

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    hI sureshg2498 ,

    Please, create a new calculated column by this measure:

    Custom Index = 
VAR CurrentCountry = 'Table'[Country]
VAR CurrentQuarter = 'Table'[Fiscal Quarter]
VAR CurrentIndex = 'Table'[Index]
VAR SortedTable =
    FILTER (
        'Table',
        'Table'[Country] = CurrentCountry && 
        'Table'[Fiscal Quarter] = CurrentQuarter
    )
RETURN
    RANKX(SortedTable, 'Table'[Index], , ASC, Dense)

     

    Now, instead to use index in your matrix, please, use Custom index column, and your final result should look like this:

     

     



    • sureshg2498's avatar
      sureshg2498
      Advocate II
      1 year ago

      Thank you for your prompt reply to my request for information. I appreciate your quick turnaround on my inquiry. Your speedy response to my information request is much appreciated. Thank you so much Bibiano.

      • Bibiano_Geraldo's avatar
        Bibiano_Geraldo
        Super User
        1 year ago

        You're very welcome! I'm glad I could assist you. Thank you for your kind words, and I'm always here to help!