Forum Discussion
Helper IIthree separate "Y axis" with shared "X axis" on linechart
Hello Everyone,
is there a way in PowerBI i can build below visual? So on he "X" axis is common "date" dimension dmn_email[sent_date_week]. Then we have 3 axis where we have measures like:
sends: DISTINCTCOUNTNOBLANK(dmn_email[dmn_email_id])
uOpens: SUM(unique_opens[unique_opens])
uClicks: SUM(unique_clicks[unique_clicks])
thanks for any help!
Hi Szokens
Yes , you can get the desired result by combining dynamic measure with small multiples.
1. Create a table with the wanted measures and order for sorting the graphs :2. Sort the "Measures" by order column:
3. Create a dynamic measure :
4. Create a wanted graph, use a table that we created to split to the small multiples and a dynamic measure as a Y axis value:
If you hava a proportions gaps like on your picture switch off the shared Y axis option:
From UX poit of view the users should know that there is an invididual y axis , ypu can use a subtitle for the note, like i did here :
The pbix with the example is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Hi, I have used solution of Ritaf1983, it work well, but DAX solution of DataNinja777 should also be applicable. I had to manually sum() unique opens and unique clicks anyway, so only this DAX solution would be okey I suppose. The only thing missed is that in small multiple options I shall choose # of columns and # of rows to achieve look I wanted so only this was ommited but I found out. thanks for help guys!
4 Replies
SzokensHi, I have used solution of Ritaf1983, it work well, but DAX solution of DataNinja777 should also be applicable. I had to manually sum() unique opens and unique clicks anyway, so only this DAX solution would be okey I suppose. The only thing missed is that in small multiple options I shall choose # of columns and # of rows to achieve look I wanted so only this was ommited but I found out. thanks for help guys!
- DataNinja777
Super User
Hi Szokens ,
Yes, you can create this visual in Power BI using a feature called Small Multiples. This feature splits a single chart into multiple panels, with each panel representing a different category, which in your case will be your three distinct measures.
To get started, you first need to prepare your data. The goal is to transform your three separate measure columns (sends, uOpens, uClicks) into a single column for the values and another column to identify the metric type. The most common way to do this is in the Power Query Editor. You would select the columns for your three measures, then use the Unpivot Columns feature. This will create two new columns: one containing the names of the original measures (e.g., "sends", "uOpens") and another containing their corresponding values. You can rename these new columns to something like "Metric Type" and "Metric Value" before clicking "Close & Apply".
Once your data is properly structured, building the visual is straightforward. Add a Line chart to your report canvas. Drag your date field (dmn_email[sent_date_week]) to the X-axis field well. Next, drag your new Metric Value column to the Y-axis. Finally, drag the Metric Type column to the Small multiples field well. Power BI will automatically render the three separate, stacked charts, all sharing the same time-based X-axis as shown in your image.
Alternatively, for users who prefer DAX, you could create a new calculated table instead of using Power Query. This approach combines the aggregated results for each measure into a single table structure suitable for the visual.
Metrics_Table = UNION( SUMMARIZECOLUMNS( 'dmn_email'[sent_date_week], "Metric Type", "Sends", "Metric Value", DISTINCTCOUNTNOBLANK('dmn_email'[dmn_email_id]) ), SUMMARIZECOLUMNS( 'dmn_email'[sent_date_week], "Metric Type", "uOpens", "Metric Value", SUM('unique_opens'[unique_opens]) ), SUMMARIZECOLUMNS( 'dmn_email'[sent_date_week], "Metric Type", "uClicks", "Metric Value", SUM('unique_clicks'[unique_clicks]) ) )Best regards,
- Ritaf1983
Hi Szokens
Yes , you can get the desired result by combining dynamic measure with small multiples.
1. Create a table with the wanted measures and order for sorting the graphs :2. Sort the "Measures" by order column:
3. Create a dynamic measure :
4. Create a wanted graph, use a table that we created to split to the small multiples and a dynamic measure as a Y axis value:
If you hava a proportions gaps like on your picture switch off the shared Y axis option:
From UX poit of view the users should know that there is an invididual y axis , ypu can use a subtitle for the note, like i did here :
The pbix with the example is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
- v-venuppu
Community Support
Hi Szokens ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you Ritaf1983 DataNinja777 for the prompt response.
I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.
Thank you.
