Create overarching level

[Bartwelvaarts]

Hi community,

I have a table with a field named level2. I have a couple dimension values over here. Then I quite a lot of other measure fields. I want to create a level 1 based on the values of level2.

Rowname1, rowname2, rowname3 from level2 should all get a new name f.e. Overarching_name1, rowname1, rowname2, rowname 3, rowname4 should all get a new name as well f.e overarching_name2. This should be all in one field because I want to show of on a pivot tabel as a dimension with subtotals of all the values that i have 

[Anonymous · Accepted solution]

Hi, @Bartwelvaarts 

Thanks for the reply from amitchandak, please allow me to provide another insight:

You'll need to create a new calculated column in Power BI Desktop that evaluates the values in the field and assigns a new overall name accordingly. You can use DAX or a function to check the value and assign a new name.

For example, use the following function:

Overarching_Level = SWITCH(
    TRUE(),
    'YourTable'[level2] IN {"rowname1", "rowname2", "rowname3"}, "Overarching_name1",
    'YourTable'[level2] IN {"rowname1", "rowname2", "rowname3", "rowname4"}, "Overarching_name2",
    'YourTable'[level2]
)

This DAX formula checks the value of and assigns it to a new overall name based on the criteria you specify. If none of the conditions are met, only the original value is returned.

Use new columns in a pivot table: Once you have a new column, you can use it as a dimension in a pivot table. This will allow you to group the data by population name and calculate the subtotals for each group.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Bartwelvaarts , Based on what I got seem like you need to use calculation group with a custom column

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0

Now you can create Calculation Groups on the desktop, there is an option under Model Tab.

Model explorer public preview with calculation group authoring| Measure Slicer: https://youtu.be/VfxfJJ0RzvU