Repeating a chart dynamically based on a category in Power BI Report Builder
This blog demonstrates how to loop a chart based on a category in Power BI Report Builder. It splits a visual into multiple versions of itself, presented side-by-side, with its data partitioned across these versions by a chosen dimension, similar to small multiples in Power BI.
Here are the simple steps that will help to get there.
Step1: Open Power bi report builder.
Step 2: To add a power bi dataset connection, left-click on the ‘Data Sources’ option.
Step 3: Select the required dataset that have been already published in the workspace and click ‘select’.
Step 4: To add dataset, right-click on ‘Datasets’ and select ‘Add Dataset’.
Step 5: Select the required data source and then click the option ‘query designer’.
Step 6: The DAX query must be pasted into the query designer. Here are a few simple methods to quickly get the DAX query.
Step 7: Open the relevant Power BI dataset and select the data that is needed on a table visual.
Step 8: Select ‘Performance analyzer’ from the view tab, then click ‘start recording, refresh visuals’, and expand the table by clicking the “+” sign.
Step 9:To copy the query select the ‘Copy query’ option.
Step 10: A popup screen will appear when the ‘Query Designer’ option is selected.
Step 11: Select the ‘Design Mode’ Option and paste the DAX query which is copied.
Step 12: Now modify the copied DAX Query to retrieve all data rather than only the top 502 records.
// DAX QueryDEFINEVAR __DS0Core =SUMMARIZECOLUMNS(ROLLUPADDISSUBTOTAL(ROLLUPGROUP('Orders'[Category], 'Orders'[State], 'Orders'[Segment]), "IsGrandTotalRowTotal"),"SumProfit", CALCULATE(SUM('Orders'[Profit])))
VAR __DS0PrimaryWindowed =TOPN(502,__DS0Core,[IsGrandTotalRowTotal],0,'Orders'[Category],1,'Orders'[State],1,'Orders'[Segment],1)
EVALUATE__DS0PrimaryWindowedORDER BY[IsGrandTotalRowTotal] DESC, 'Orders'[Category], 'Orders'[State], 'Orders'[Segment]
Step 13: Change EVALUATE __DS0PrimaryWindowed to EVALUATE __DS0Core to get all records in the above DAX query and then click ‘Ok’.
// Modified DAX queryDEFINEVAR __DS0Core =SUMMARIZECOLUMNS(ROLLUPADDISSUBTOTAL(ROLLUPGROUP('Orders'[Category], 'Orders'[State], 'Orders'[Segment]), "IsGrandTotalRowTotal"),"SumProfit", CALCULATE(SUM('Orders'[Profit])))EVALUATE__DS0Core
ORDER BY[IsGrandTotalRowTotal] DESC, 'Orders'[Category], 'Orders'[State], 'Orders'[Segment]
Step 14: Left-click the mouse on the white space and select insert->Table.
Step 15: Remove the two columns as well as the data row from the table.
Step 16: Drag and drop the field (based on which the chart needs to be looped) into the empty cell in the Row Groups window.
Step 17: After adding the series(Eg : Category column) to the Row Group, a blank cell appears next to it. Left-click the box and select Insert -> Chart.
Step 18: Choose the required chart.
Step 19: By clicking and dragging towards the table’s end, The size of the chart can be changed accordingly.
Step 20: When a chart is selected, a screen displays on the right side of the table, in which the chart data (Values,Category Groups,series Groups) should be entered.
Step 21: Click ‘Run’.
Step 22: The Chart will be looped by the field entered in the Row Group, and a report will be generated from it.
Finally the chart will be looped dynamically based on the field entered.This method allows to loop a chart based on a field, which saves a lot of time and effort compared to manually inserting charts.
About us:
Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe and we’re quickly building a brand that is well known for superior delivery.
Website : https://bi3technologies.com/
Follow us on,
LinkedIn : https://www.linkedin.com/company/bi3technologies
Instagram : https://www.instagram.com/bi3technologies/
Twitter : https://twitter.com/Bi3Technologies
