How to change table visual data

[Anonymous]

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.

[v-xinruzhu-msft]

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.

[Ahmedx · Accepted solution]

pls try this

[Anonymous]

I cannot download the file.

[VahidDM]

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]

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]

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

Cheers,

Vahid

[danextian]

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]

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.