Hi @seba ,
You can effectively shape your data to visualize device distribution over time using two primary methods in Power BI: Power Query for pre-processing the data, or DAX for creating dynamic calculations. The Power Query approach is often recommended for this type of transformation as it cleans and structures the data at the source, which can lead to better report performance.
To begin with Power Query, you would first open the Power Query Editor from the "Transform data" option on the Home tab. The initial step is to isolate the maximum span value for each device on each report date. This is achieved by using the "Group By" feature. You would configure it to group by both the Report Date and Device name columns. Then, you would create a new column, named for instance Max Span, by applying the Max operation to your Span column. This action condenses your data, leaving only the single highest span value for every unique device on a given date.
Following the initial grouping, the next step is to categorize these maximum span values into defined buckets. This is done by adding a "Conditional Column" from the "Add Column" tab. You can name this column Span Bucket and create a series of rules to assign each Max Span value to a bucket. For example, you would set a rule where if Max Span is greater than or equal to 40, the value is "40-50", and another rule where if it's greater than or equal to 30, the value is "30-40", and so on. It is important to order these rules from highest to lowest to ensure correct categorization.
Once the buckets are defined, the final transformation step in Power Query is to count how many devices fall into each bucket for each report date. You would use the "Group By" feature again, this time grouping by Report Date and the newly created Span Bucket column. You would add a new column, perhaps called Device Count, using the Count Rows operation. After completing these steps, you can click "Close & Apply" to load the perfectly shaped table—containing Report Date, Span Bucket, and Device Count—into your Power BI model, ready for visualization.
Alternatively, if you prefer a more dynamic approach within the Power BI report view, you can use Data Analysis Expressions (DAX). First, you would create a new calculated table. Navigate to the Data view, select "New table," and use a DAX expression to produce a summarized table that contains the maximum span value for each device and date.
MaxSpanPerDevicePerDate =
SUMMARIZE(
YourTableName,
YourTableName[Report Date],
YourTableName[Device name],
"Max Span", MAX(YourTableName[Span])
)
After creating this table, you would add a calculated column to it for the span buckets. Select the new table and click "New column," then input a DAX formula using the SWITCH function to assign each Max Span value to its respective bucket.
Span Bucket =
SWITCH(
TRUE(),
[Max Span] >= 40, "40-50",
[Max Span] >= 30, "30-40",
[Max Span] >= 20, "20-30",
"Other"
)
With the data prepared using DAX, you can build your visual. You will need a measure to count the devices. You can create one by right-clicking on your new table and selecting "New measure."
Device Count = COUNTROWS('MaxSpanPerDevicePerDate')
For the visualization itself, a stacked column chart or a line chart would be highly effective. For a stacked column chart, you would place Report Date on the X-axis, the Device Count measure on the Y-axis, and the Span Bucket column in the Legend field to see the distribution within each bar. For a line chart, the configuration would be the same, which would allow you to clearly see the trend of each device bucket over time.
Best regards,
