Adding data as rows for custom calculated view

[maxwill83]

Hi. I am fairly new to Power BI. I can create the visualisations and some simple measures so far, but got stuck into a problem that seems simple, but I can find a direct answer to. I upload a spreadsheet to Power BI with the number of students by date (day, month and year) with ID, region and status. I use the region field as a slicer to see the distinct count by region for all the fields, for example, if they completed the course or not. I managed to create an individual sum of all fields by whichever status I need (below), but everything I add doesn't show in the slicer, as it's not part of that table. So, I think I need to add some calculated rows to the data to be able to see in the slicer? I am displaying the data as a line chart (time series).

Total ID 1 = 
CALCULATE( DISTINCTCOUNT('Table01'[ID]), 
ALL('Table01'[Completion Status]) )

My question is how do I create calculated rows after data was imported into Power BI that will do a distinct count for the data for all regions by date, that I call "National" and also group a special region called "North districts" that includes just 3 of those regions? (both should appear as an option in the slicer to be selected, the new National and North Districts, along with the rest of the districts). 

So the raw data upload would have 12 regions, then once in Power BI, it would sum the distinct count of all regions and create a National row with the same dates that are available for the other regions that I can select on the slicer and a custom group with 3 regions that I can also select from the slicer (summing those 3 regions distinct counts too). Then every time I update the data, it updates all of those automatically. I don't have a number column, I am counting the number of distinct IDs I have in the data. Short example:

Slicer for time series to select:       National | North districts | Nicefields | Badfields | Worsefields

[Line chart]

Example of raw data:

Completion Date	ID	Name	Region	Completed Status
25/03/2022	YY66	Aaron	Nicefields	Pass
21/05/2023	YU78	Maria	Badfields	Fail
15/02/2023	TY43	Martin	Nicefields	Pass
16/07/2023	YT56	Heron	Worsefields	No show
10/09/2022	HY67	Hercules	Badfields	No Show

I'm sure there's a smarter way to do this. If anyone could help, it would be appreciated.

Thank you in advance.

[isjoycewang · Accepted solution]

Hi @maxwill83 ,

Try to create a definition table for your region and connect it to the original table.

So that you can put them into the slicer and see all the options you want.