Forum Discussion
need help with visualization and data prep.
Hello everyone,
I have data like below in table and need visualization as shown in the image.
Problem also is, if a Product got delisted and new product got listed so no new rating data should apper for that product in the graph.
For Eg.
Product B0777J1KP1 got delisted on 25/04/2018 when the new product B07CN9D18C listed so ratings of the product B0777J1KP1 after this date is irrelevant.
| Artikelnummer | Product | Country | Product_Coutry_Art | Aktiv | rating | Creation_date | Rating_Date |
| 10029872 | B0777J1KP1 | DE | B0777J1KP1DE10029872 | 0 | 2 | 07/11/2017 | 13/12/2017 |
| 10029872 | B0777J1KP1 | DE | B0777J1KP1DE10029872 | 0 | 3.1 | 07/11/2017 | 18/12/2017 |
| 10029872 | B07CN9D18C | DE | B07CN9D18CDE10029872 | 0 | 0 | 25/04/2018 | 25/04/2018 |
| 10029872 | B0777J1KP1 | DE | B0777J1KP1DE10029872 | 0 | 3 | 07/11/2017 | 26/04/2018 |
| 10029872 | B0777J1KP1 | DE | B0777J1KP1DE10029872 | 0 | 2.7 | 07/11/2017 | 29/01/2019 |
| 10029872 | B07MCKXF9X | DE | B07MCKXF9XDE10029872 | 0 | 5 | 11/01/2019 | 25/02/2019 |
| 10029872 | B07MCKXF9X | DE | B07MCKXF9XDE10029872 | 0 | 2.9 | 11/01/2019 | 09/05/2019 |
| 10029872 | B07MCKXF9X | DE | B07MCKXF9XDE10029872 | 0 | 2.6 | 11/01/2019 | 16/07/2019 |
| 10029872 | B07MCKXF9X | DE | B07MCKXF9XDE10029872 | 0 | 2.4 | 11/01/2019 | 05/08/2019 |
| 10029872 | B07MCKXF9X | DE | B07MCKXF9XDE10029872 | 0 | 2.6 | 11/01/2019 | 23/08/2019 |
| 10029872 | B07YSD8DMF | DE | B07YSD8DMFDE10029872 | 1 | 0 | 07/10/2019 | 07/10/2019 |
Hi A31,
It is hard to analyse irregular data in Powerbi(as I know, data will aggregate automatically in chart), you could use below measure to get similar line chart or area chart, but there is no date for 0 , so it will show a point in line chart or area chart. If you want it to show as a line, you need at least two point, you could add row in model, which should work
10029872 B07CN9D18C DE B07CN9D18CDE10029872 0 0 11/01/2019 11/01/2019 Measure = CALCULATE(SUM('Table'[rating]),FILTER( ALLEXCEPT('Table','Table'[Product]),MIN('Table 2'[Date])<='Table'[Rating_Date]&&MIN('Table 2'[Date])>='Table'[Rating_Date]))Or you also could ask other contributors in forum for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi A31,
According to your description, it seems that you want show continual date? The part in blue circle will show 0?
And if the Aktiv =1, you need to show value till today(), right? If this is not what you want, please correct me. I need to research on it and will inform you as soon as I get it.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Zoe,
Data in the blue circle should not appear on the graph since B07CN9D18C already has started before that. After 25-04-2018 only data for B07CN9D18C should show on the graph. and after 11th jan 2019, B07CN9D18C should go out and B07MCKXF9X should start and continue until some new product got listed, like here we have on 01-10-2019 (B07YSD8dMF).
For the active yes we need it till today.
Hi A31,
It is hard to analyse irregular data in Powerbi(as I know, data will aggregate automatically in chart), you could use below measure to get similar line chart or area chart, but there is no date for 0 , so it will show a point in line chart or area chart. If you want it to show as a line, you need at least two point, you could add row in model, which should work
10029872 B07CN9D18C DE B07CN9D18CDE10029872 0 0 11/01/2019 11/01/2019 Measure = CALCULATE(SUM('Table'[rating]),FILTER( ALLEXCEPT('Table','Table'[Product]),MIN('Table 2'[Date])<='Table'[Rating_Date]&&MIN('Table 2'[Date])>='Table'[Rating_Date]))Or you also could ask other contributors in forum for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.