I'm trying to use Power BI, with Power Query, to summarize some data for an SSRS/Paginated Report. What I want to display to the user in the report, is a table with counts of list items. Columns will be grouped by a field called "Office", and rows will be grouped by another field called "AgeGroup", along with a Totals row.
What I want out of PowerBI/Power Query is a recordset with three columns, "Office", "AgeGroup", and "Count". My issue right now, is if there are zero list items that match a given combination (for example, zero items that have both an "Office" field value of "OPQ", and an "AgeGroup" field value of "< 5 years old")) then there will be no line in my result table for that combination ("OZR" and "< 5 years old") when what I want is that line to be present, but with a "Count" value of zero. In other words, if I have 15 different values for "Office", and 3 possible values for "AgeGroup", I want my result recordset to have 45 rows (15*3 = 45). I'm hoping there is a formula or expression that can be used to solve my problem.
I believe using the LookupSet in SSRS/Paginated Report could accomplish this as well, however I thought this would be a good opportunity to learn something new about Power BI / PowerQuery.
EDIT: Some images to explain, I hope.
Assume I have a total of three offices that should be displayed in the final report (OfficeA, OfficeB, & OfficeC).
First image: My "dataset":
(Important - note the absence of "OfficeC" in the dataset.
Second image: What I would like as output from Power Query, to be consumed as a Dataset in SSRS/Paginated Report:
Third image: What I want displayed in my Paginated Report to the end user:
No comments:
Post a Comment