Need help

[yousaf]

Hi Guys,,

I am trying to replicate a web report in power bi. There is a graph which is shows the overall equipment rating based on tests done every month, rating being Legend here. Now the problem is, not all equipment are tested every month and when i plot, i just get some number of tests done on equipment in a month. I need record against all equipment every month corresponding to the rating. at that time. Any leads? 

 DataGraph

need something like this

TIA!

[Ashish_Mathur]

Hi,

The best way to solve this problem is to show the expected result in a Table.  Once the Table is ready, we can always switch the visualisation to any other that you want. 

[yousaf]

Thanks Ashish.

I tried this. Table is fine but when i switch visual to stacked column, it doesnt work. 

[yousaf]

I'll try to explain.

I have 100 equipment. Every equipment has different test frequency. In a given month, not every equipment is tested but I still have last known rating of the equipment for which there is no test in that specific month. Now what I want to show (as in Picture 3 in my original post) is that for each month, what was the latest ratings for all 100 equipment. This way, the total number will remain 100 every month in the grapgh, but the rating distribution (color based on legend) will change based on last available rating.

for example, in january, 35 equipment were tested. So i have latest ratings on these 35 equipment. But as the rest of the equipment also have previous rating, the graph needs to show all 100, with last available rating.

I hope it is more clear now.

[Jihwan_Kim]

Hi,

Please correct me if I wrongly understood.

When I saw your desired outcome picture, I thought the gray-colored-area, that you also wanted to display, is a "not-tested-equipment-count".
If I am not wrong, in this case, I usually create one more data point for "others", for instance, and create a DAX measure that shows numbers for "others".
If it is OK with you to share your data with me, perhaps I can try to create a calculated measure to describe in a stacked column chart.

Thanks.

[yousaf]

I don't know how to share data here

[yousaf]

Hi,

Thanks for the reply.

That grey area is also a rating.

Lets say we have 10 equipment. All 10 were tested in December. Then 5 were tested in January, 9 were tested in February. In March, 7 were tested and got rated x, y or z. Now what i need to show is the count of all 10 equipment and there rating (as legend) for January, February and March & so on. At the moment, I'm just getting count of equipment tested in a specific month, which does not give me an overall equipment rating picture.

[Jihwan_Kim]

Hi,

Thank you very much for your explanation, and I also got your sample data by message.

Thank you.

Sorry to ask many questions to your question, but if you do not mind, could you please check whether you want to show the information like in the below picture in a stacked bar chart, or am I still missing something?
Terribly sorry that it doesn't look like meet your requirement, but if I wanted to show all the information in the stacked bar chart, I had no choice but to come up with like the below picture.

[yousaf]

Hi,

Unfortunately this is not what i need. What I'm after is one bar for all equipment, colored according to the rating, against each month. Similar to the picture3 in my original post.

I've tried many measures, but the date filter just isnt allowing for all equipment to show in one bar for a specific month.

[Jihwan_Kim]

Hi,

Thanks for your feedback.

I am sorry that my visualization was not the one.

However, if your problem was related to the date-month concern, can you try to switch the format of your date? For instance, instead of writing dd-mm-yyyy, try mm-dd-yyyy. It depends on what format is your computer system using, but if you face a problem that does not correctly consolidate the month,  sometimes it is because of the date-format-issue.

Thank you very much.

[yousaf]

I have 100 equipment. Every equipment has different test frequency. In a given month, not every equipment is tested but I still have last known rating of the equipment for which there is no test in that specific month. Now what I want to show (as in Picture 3 in my original post) is that for each month, what was the latest ratings for all 100 equipment. This way, the total number will remain 100 every month in the grapgh, but the rating distribution (color based on legend) will change based on last available rating.

for example, in january, 35 equipment were tested. So i have latest ratings on these 35 equipment. But as the rest of the equipment also have previous rating, the graph needs to show all 100, with last available rating.

I hope it is more clear now.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[yousaf]

Link sent.

[Ashish_Mathur]

Share the link here.

[yousaf]

https://1drv.ms/u/s!Aq7V-SCS2lDDgZsYfWkH9_G0g802EA?e=sPB0Iq

[Ashish_Mathur]

Hi,

Is this the result you are expecting?

[yousaf]

Yes. Exactly.

Jihwan's solution also worked on the sample data but isnt working on the main data. Plus, equipment will be filtered based on system, so keeping equipment constant also doesnt help.

[Ashish_Mathur]

Hi,

Share your main data in an MS Excel file.  I only need the first 3 columns in that file (not the bins column.  I have solved it without the bins column).  Let me plug in that data to see whether my solution get your desired result or not.

[yousaf]

Here is the link,

https://1drv.ms/x/s!Aq7V-SCS2lDDgZsZV7wv5EV4faulgw?e=ajNHRc

[Ashish_Mathur]

Hi,

Is this the result you expect to see for 2020 and Plant Z?

[yousaf]

Yes. This is correct.