Creation of a table with several lines and sublines (but no pivot table)

[matbauwin]

Hi everyone,

I would like to group several tables in one unique table, but without creating a pivot table as it does not enable to present data as I want (exept if I missed something).

Here is what I have created so far (5 different tables) but I want to have only one with several sections (region, county, Thematic, etc.) is it possible ?

[Anonymous]

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

Hi, @matbauwin 

Could you please let us know if MFelix‘s response resolved your issue? If it did, kindly accept it as the solution.

Regarding the issue you raised, my solution is as follows:

1.Firstly, I created the following sample data:

2.Secondly, you can create the following calculated table:

Table2 = 
UNION (
    SELECTCOLUMNS (
        'Table',
        "Type", "Region",
        'Table'[Region],
        'Table'[Encours],
        'Table'[Concentration],
        'Table'[Limite]
    ),
    SELECTCOLUMNS (
        'Table (2)',
        "Type", "Pays",
        'Table (2)'[Pays],
        'Table (2)'[Encours],
        'Table (2)'[Concentration],
        'Table (2)'[Limite]
    ),
    SELECTCOLUMNS (
        'Table (3)',
        "Type", "Focus thematique",
        'Table (3)'[Focus thematique],
        'Table (3)'[Encours],
        'Table (3)'[Concentration],
        'Table (3)'[Limite]
    ),
    SELECTCOLUMNS (
        'Table (4)',
        "Type", "Type de produits",
        'Table (4)'[Type de produits],
        'Table (4)'[Encours],
        'Table (4)'[Concentration],
        'Table (4)'[Limite]
    ),
    SELECTCOLUMNS (
        'Table (5)',
        "Type", "Type de produits",
        'Table (5)'[Organiation],
        'Table (5)'[Encours],
        'Table (5)'[Concentration],
        'Table (5)'[Limite]
    )
)

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[MFelix]

Hi @matbauwin ,

You can create a table with the values and categorization you need something similar to this:

Type	Detail
Region	Afrique
Region	Amérique latine et Caraíbes
Pays	Bénin
Pays	Guatemala
Pays	Rwanda
Pays	Sénégal
...	...

Now create a measure similar to this one:

Encours Values = 
SWITCH ( SELECTEDVALUES(Table[Type]),
      "Region", CALCULATE([Encours], RegionTable[Region] in VALUES([Table[Detail]),
      "Pays", CALCULATE([Encours], PaysTable[Pays] in VALUES([Table[Detail]),
      ...
      "Organisation", CALCULATE([Encours], OrganisationTable[Organisation] in VALUES([Table[Detail])
)

Create a similar one for the Concentration, the Column you should use in the Second part of the calculate is the column that you have on  your model so for the Region you should pick up the current column you are using on the matrix you present on the image.

Now setup your matrix with the hierarchy of type and detail and the values for the metrics