Months in fields/columns in Table visuals

[magyar]

It would be easier if it was possible to show screenshots or attach files...or is that possible?

Anyway, I would like to transform this:

Sales    Months  Shop

$1          Jan          A

$2          Feb         A

to this in a Table or Matrix Visual:

Shop   Jan     Feb   

A         $1       $2

Any hint or ideas are very much appreciated.

David

[TomMartens]

Hey,

as you can see, what you are looking for can be achieved using the Matrix-Visual (so it's possible to attach screenshots to the post)

Here you can find the example pbix, as you can see it's not possible to attach any other files besides images, txt-, xlsx-, or pbix-files have to be shared using a link.

Hope this helps

[afreenn04]

Thank you. You are genius. 

[magyar]

Thanks guys. I have tried what you have suggested before but it doesnt seem to work. Please see attched pictures:

This is before pivoting the date field...

and this is after. As you can see it does the job in terms of pivoting the table on the date field but the values for "OverallStatus" have changed from text to 1s and 0s. Not sure why?

Thanks for trying to help.

David

[s15]

It would be better if you could give the dataset. You asked another dataset which showed Month only. Now your dataset is totally different. Please share your expected outcome in Excel file.

Thank you.

[magyar]

Fair enough, I have tried simplify the problem.

I have attached a link to the file, hope it works. 

https://www.dropbox.com/s/gpu4xyi2b50w175/Matrix%20Visual.pbix?dl=0

Thanks,

David

[TomMartens]

Hey David,

to me it seems you have to build a new table - I would call ProjectStatus, the columns would be

ProjectID (maybe it is Projectname) | Date | StatusID | StatusImageUrl

And it also seems to me that you should also use a separate Calendar Table

The Relationships then sould be

Project (one) to ProjectStatus (many)

Date (one) to ProjectStatus (many)

Status (one) to ProjectStatus (many)

I'm not sure but to me it seems that you are somewhat trapped in your current table ProjectTable.

This table seems to be  one table fits all questions approach.

It is always a good approach to separate tables in a star schema like approach, meaning build dimension tables and fact tables. A fact table holds data about measured objects (status about a project during a certain point of time), whereas dimension tables provide information about organisational objects you want to analyze (your project). Basically dimension tables have hold just one instance of these objects, with its describinng attribute. Whenever the value of one these attibutes changes its value and you have to track this change, then you have to think about a fact table (sales amount for a product changes over time, or the status of a project changes over time).

Currently I'm not able to deduct why the status column in the report you thankfully provided is called "May" and how the Status for June should be calculated.

Guess I'm missing a lot to provide a solution for your problem

Regards

[s15]

Hi @magyar

You can pivot the Month column. Do the following things:

Step 1: Click table icon on the left panel. Select Edit Queries > Edit Queries.

Step 2: When the Query Editor opens, click Transform tab on the Ribbon. Click Month column. Click Pivot Column.

Step 3: Select sale under Values Column. Click OK

Step 4: Click Home tab. Click Close and Apply to make changes.

By the way, you can attach the photo by click photo icon..

If this is helpful please accept as a solution. Thank you.