Matrix and agregate

[aawde]

Hi,

I have an Excel dataset featuring variables such as "Indicateur" with various values and "Region," encompassing both cities and the country name (Canada). The dataset covers the period from January 1, 2020, to November 1, 2023, with the variable "Values."

The objective is to present this data in a cross-matrix format, where columns represent years and months, rows depict indicators, and cities appear as sub-lines under each indicator. When expanding an indicator, only the cities within the country should be visible. For the country, the existing data in Excel must be displayed, and I am currently unable to specify Canada as an aggregate line for these cities.

I have attached a sample of the current data along with an illustration of the desired output. Your assistance in achieving this presentation is highly valued.

[aawde]

Thank you for your prompt response; yes, that is what I was searching for. Perhaps I overlooked mentioning that the dataset includes other countries, such as the USA with cities like New York and Boston, and France, among others.

Additionally, I would like to be able to expand the date column since currently only the year is displayed. For yearly and quarterly intervals, we intend to display averages. This way, users can expand the date to view either trimesters or months.

I appreciate your assistance greatly.

[Anonymous · Accepted solution]

Hi @aawde ,

Please try below steps:

1. below is my test table

Table:

2. create measure with below dax formula

Sum for Valeur = SUM('Table'[Valeur])

Canada Sum =
VAR _a =
    CALCULATE ( [Sum for Valeur], FILTER ( 'Table', [Region] = "Canada" ) )
VAR _b =
    IF ( ISINSCOPE ( 'Table'[Region] ), [Sum for Valeur], _a )
RETURN
    _b

3. add a martix visual with table fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.