Sum a mesure with conditions

[Pier2]

Hello,

I try to sum the value of a mesure (not a calculated column) for a giving year and category of company.

At the end, the companies of the same category, for the same year, should have the same total.  Filtering and removing the filter doesn't work. Otherwise, I end up with exact individual value of each company.How can I achieve that?

Company	Category
AA	Tech
BB	Bank
CC	Bank
DD	Bank

Company	Year	Revenus
AA	2023	500
DD	2024	600
BB	2024	300
CC	2024	200

Company	Mesure_value_A Year 2024
AA	1
BB	5
CC	8
DD	2

Result expected,  for company of same category, for 2024

Company	Sum by category of Mesure_value_A
BB	15
CC	15
DD	15

[v-linhuizh-msft]

Thanks for the reply from DataNinja777.

Hi @Pier2 ,

Based on your description, I created relationships for these tables:

Create a measure:

Measure = SUMX(FILTER(ALLEXCEPT('Revenue','Category'[Category]),'Revenue'[Year]=MAX('Revenue'[Year])),[Mesure_value_A Year])

Use the company field from the table on the many-side as the column for the visual.

Best Regards,
Zhu

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

[Pier2]

Hello @v-linhuizh-msft ,

Unfortunately, your proposition isn't the solution yet. I still working on it.

To help understad anyone reading this post, I add a picture of the relations between the table. It may surely help.

The table Mesures is filled with all kind of measures that works right.

[DataNinja777]

Hi @Pier2 ,

To achieve the desired result, you need a DAX measure that sums Mesure_value_A for all companies in the same Category and Year while ensuring that each company in the same category receives the same total. The key is to remove the filter on Company while keeping the filters on Category and Year. You can use the CALCULATE function along with ALLEXCEPT to achieve this:

Sum by Category of Mesure_value_A =
CALCULATE(
    SUM('YourTable'[Mesure_value_A]),
    ALLEXCEPT('YourTable', 'YourTable'[Category], 'YourTable'[Year])
)

This measure ensures that the sum is calculated over all companies within the same category and year, effectively distributing the same total to each company in that group. When applied to a table visualization, companies belonging to the same category in the given year will display identical totals.

Best regards,

[Pier2]

Hello @DataNinja777 ,

Thanks for your help. I've try your formula, but I'm sorry to say that it didn't work.

SUM doesn't mix with a mesure.

The year and the category are in two different tables and cannot fit together in AllExcept. 

AllExcept and other type function to remove a filter doesn't help, so far.

We are probably close to the right answer.

Here's the real code that works so far. The results are wrong but I got something.

VAR somme_x= 
CALCULATE(
    SUMX(Mesures,[m026_indiceVCICapitauxPropres]),
    tblEntreprise[lngEntrepriseCategorie]=categorieSelectionnee,
    CalendrierAnnuel[Année fiscale]= anneeSelectionnee
)

 I got the list of companies but with their individual mesure value.

[Pier2 · Accepted solution]

The solution is:

    CALCULATE(

        SUMX(

            FILTER(

                ALL(tblEntreprise),  -- Retirer les filtres sur les entreprises

                tblEntreprise[lngEntrepriseCategorie] = categorieSelectionnee  -- Limiter aux entreprises de la catégorie sélectionnée

            ),

            [m026_indiceVCICapitauxPropres]  -- Calculer la mesure pour chaque entreprise

        ),

        CalendrierAnnuel[Année fiscale] = anneeSelectionnee  -- Appliquer le filtre sur l'année

    )