Use measures instead of 'Value' column after unpivoting

[Anonymous]

Hi,

I have a huge table with many columns and I need to create a very unusual looking report based on that one table only. I am open to any changes in my model. My table (simplified) looks somehow like this:

Row category 1

Row category 2

Row category 3

Some text values

Some number values

Some datetime values

Now what what's expected from me is to make a matrix that have three row categories, some columns and some subcolumns. As I found in many places, for example here I unpivoted my columns, added some categories and subcategories and wanted to present everything in my matrix visual. So now my table looked like this:

Row category 1

Row category 2

Row category 3

Column category

Column subcategory

Value

Great, but now in my "Value" column i got everything mixed; dates, number values, percent values, text names for "Row category 2" and "Row category 3" levels and so on. I can't really do much with what I got, and I need a lot:

1. Names for categories should be displayed only on their levels, please refer to followin links:
   https://community.powerbi.com/t5/Desktop/Disable-Subtotal-for-Text-Column-Matrix/td-p/477611/page/2
   https://community.powerbi.com/t5/Desktop/Remove-subtotal-from-only-one-column-in-a-Matrix-in-Power-B...
   https://community.powerbi.com/t5/Desktop/Only-Total-Select-Values-in-a-Matrix-amp-Formatting-Help/m-...
   In my case there are names for categories on second and third levels and should be displayed in correct rows (top level rows should be blank).
2. I need columns of different types, text, numbers, percents.
3. Calculate sums, averages and differencies between certain subcolumns in scope of one column group.
   This for example produces error "Cannot calculate average of text column" because of miscellaneous data in Value column (filters applied should filter out only R8 related fields which are numbers but still):

avg = 
    CALCULATE (
    AVERAGE ( WORK_STATUSES[Value] );
    CALCULATETABLE (
        VALUES ( WORK_STATUSES[Attribute] );
        WORK_STATUSES[Attribute] = "R8 delta";
        WORK_STATUSES[L1] = "R8"
    )
)​

I'm stuck, I have no idea what to do from where I get or maybe to make few steps back. I'm new to Power BI and to BI in general, please, explain everything to me in simple steps.

Thank you in advance for any help, I know I'm asking for much but please understand my situation.

[v-frfei-msft]

Hi @Anonymous,

Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.