cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Need help

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!

1 ACCEPTED SOLUTION
Super User

Hi,

I hope the below is what you are looking for.

I created a measure that gives The LATEST Rating result of the equipment among the current or from before until the current month. I also considered if one Equipment has two Ratings on the same day, then give the maximum result of Ratings, because "selectedvalue(Data[Rating])" gives blank value if it has two results.

test result (if it is not in this month, show last month result) =
VAR lasttestdate =
CALCULATE (
MAX ( Data[Test_Date] ),
FILTER ( ALL ( dates ), dates[Date] <= MAX ( dates[Date] ) )
)
VAR withresult =
CALCULATE ( SELECTEDVALUE ( Data[Rating], MAX(Data[Rating]) ), dates[Date] = lasttestdate )
RETURN
withresult

Then, I created the below measure.

equipment count by test result =
VAR newtable =
SUMMARIZE (
ALL(Equipments),
Equipments[Equipment],
"@testresult",
[test result (if it is not in this month, show last month result)]
)
VAR filtertable =
FILTER (
newtable,
[@testresult]
= SELECTEDVALUE ( Ratings[Rating] )
)
RETURN
IF (
ISBLANK ( [count of equipment] ),
BLANK (),
COUNTROWS ( filtertable )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

28 REPLIES 28
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Thanks Ashish.

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

Helper I

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.

Super User

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper I

I don't know how to share data here

Helper I

Hi,

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.

Super User

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper I

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.

Super User

Hi,

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper I

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.

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I
Super User

Hi,

Is this the result you are expecting?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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.

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Yes. This is correct.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors