Skip to main content
cancel
Showing results for 
Search instead 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

Reply
yousaf
Helper I
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? 

 

 DataDataGraphGraph

 

need something like this

 

 

yousaf_0-1616196896911.png

TIA!

1 ACCEPTED SOLUTION

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 )
)
 
Picture1.pngPicture2.png
 

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.


Go to My LinkedIn Page


View solution in original post

28 REPLIES 28
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

Thanks Ashish.

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

yousaf
Helper I
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.

Jihwan_Kim
Super User
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.


Go to My LinkedIn Page


I don't know how to share data here

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.

 

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.

 

Picture1.png

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.


Go to My LinkedIn Page


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.

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.

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.


Go to My LinkedIn Page


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.

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Link sent.

Share the link here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

Is this the result you are expecting?

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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
https://www.linkedin.com/in/excelenthusiasts/

Hi,

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Yes. This is correct.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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