Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to change table visual data

Hi, I need help in transforming below table visual into a different format.

 

Currently, this is how it looks like.

 

I want to transform it into below format.

 

So instead of a row for each vendor, I want to make it to number of vendors for that group.

 

I already tried to use distinctcount DAX but when I use a filter, it will only cover the top expense and not the expenses within the specified range.

Need Help

8 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi Anonymous ,

     

    While the table visual is active, change the summarization for Vendor column.

    Alternatively, you can create a measure that counts the number of rows in a table and use that in your table instead  of vendor column

    row count =
COUNTROWS ( data[vendor] )

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I actually did this however when I choose a value from the slicer, the data that comes out is inaccurate. It will only show the top group and not all groups and vendor count for each.

  • VahidDM's avatar
    VahidDM
    Super User
    2 years ago

    Hi Anonymous 

     

    Try use this:

    #Vendor =
COUNTROWS ( VALUES ( 'Table'[Vendor] ) )

     

    Output:

     

     

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

    Appreciate your Kudos!! 

    LinkedIn | Twitter | Blog | YouTube 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I tried this however when I choose a value from the slicer, the data that comes out is inaccurate. It will only show the top group and not all groups and vendor count for each.

      • VahidDM's avatar
        VahidDM
        Super User
        2 years ago

        Can you share some information about that slicer? what column has been used in that slicer? any info re the data model?

         

        Cheers,

        Vahid

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I cannot download the file.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

    You can create the following measures

    Sum_Expense = CALCULATE(SUM('Table'[Expense]),ALLSELECTED('Table'),'Table'[Group] in VALUES('Table'[Group]))
    Sum_%Expense = CALCULATE(SUM('Table'[%Expense]),ALLSELECTED('Table'),'Table'[Group] in VALUES('Table'[Group]))
    Sum_Rute = CALCULATE(SUM('Table'[Routes]),ALLSELECTED('Table'),'Table'[Group] in VALUES('Table'[Group]))
    Sum_%Rute = CALCULATE(SUM('Table'[%Route]),ALLSELECTED('Table'),'Table'[Group] in VALUES('Table'[Group]))
    Count_Vendor = CALCULATE(DISTINCTCOUNT('Table'[Vendor]),ALLSELECTED('Table'),'Table'[Group] in VALUES('Table'[Group]))

    Best Regards!

    Yolo Zhu

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