Can I create a Table from a Matrix?

[Anonymous]

Hi! I want to create a new table related to the one I use as a database, and I want it just like a Matrix visual I already made. I'm just starting to use Power BI, and I don't know how to do this with DAX, so I was wondering if maybe I could make the table automatically from a Matrix. Do you know if this is possible?

For example I have a database of fruits and the days in a year when I ate any of those, so the matrix I created  from that DB is basically a column with the name of the fruit not repeated and columns with the "count of fruit" every month in a year.

I could export the matrix as a csv and then import it, but that wouldn't link all the details of the database to number behind "count of fruit". For example it would be interesting to filter that number by other fields I have on my database like "Type of fruit" or "Fruit color" and so on,

Thank you for your time,
I am attentive to your comments,
Best regards,
Gabriela Sofía

[MFelix · Accepted solution]

Hi @Anonymous ,

On PBI you don't need to create additional table to have filtering by other columns on your table in this caseType of fruit of fruit color.

If your database already have this columns you can create slicers, you can add the columns to the filters you can add additional columns to the matrix or other visuals and filter out the information on the matrix.

Check the documentation about slicers and filters to have an idea on how you can interact within your report, also check the interactions page for detail on how visuals interact between them.

If you need any further assistance please tell me.

Regards,

MFelix

[MFelix · Accepted solution]

Hi @Anonymous ,

On PBI you don't need to create additional table to have filtering by other columns on your table in this caseType of fruit of fruit color.

If your database already have this columns you can create slicers, you can add the columns to the filters you can add additional columns to the matrix or other visuals and filter out the information on the matrix.

Check the documentation about slicers and filters to have an idea on how you can interact within your report, also check the interactions page for detail on how visuals interact between them.

If you need any further assistance please tell me.

Regards,

MFelix

[Anonymous]

Hi @MFelix but what if I do a new table summarizing the database; if I make a visual of this table, could I filter it by the categories on the database?

[MFelix]

Hi @Anonymous ,

You can add a slicer a filter on the visual or with the interactions between visuals, so this will make the filtering based on your database.

Regards,

MFelix

[Anonymous]

I see... so I wouldn't need to create all those fields to filter the data of the new table.
Well, the problem is, I still can't solve the initial problem. I mean, I made the matrix in the first place because I need a calculated value from that matrix and a visual from that calculation.

To give a little more context about this, I'm gonna make it on excel, for I don't know how to on PBI. Here's a table of the count of fruit by month and by category of color (r=red, g=green, b=blue).


So, from this blessed table (the famous matrix on PBI) I calculate the average on every column to make a chart of the average of the times a fruit was eaten by month and by category, like this
:

So finally the question is: how can I make this chart on PBI?

Best Regards,
Thank you for your time and your patience @MFelix 
I will be attentive to your comments,
Gabriela

[MFelix]

Hi @Anonymous,

Can you confirm the your table is like this:

Fruit - Colour - Month


Regards,
MFelix

[Anonymous · Accepted solution]

I finally got it, I made a measure:

MeasureAVG = count(BD[Fruits])/DISTINCTCOUNT(BD[Fruits])

and then I put that measure on a Line chart in Values and on Legend I put the Color and on Axis I put the months,

Best regards,