Forum Discussion

yousaf's avatar
yousaf
Helper I
5 years ago
Solved

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!

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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 )
    )
     
     

28 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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's avatar
      yousaf
      Helper I
      5 years ago

      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's avatar
        Jihwan_Kim
        Super User
        5 years ago

        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.

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    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's avatar
      yousaf
      Helper I
      5 years ago

      Thanks Ashish.

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

  • yousaf's avatar
    yousaf
    Helper I
    5 years ago

    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.