How to sumifs instead of lookup with summarize table

[o59393]

Hi

I created the following table using this summarize expression:

The issue i have is that the column % should be summing 100%, however the combination of columns tier 1 and tier 2 contains duplicates with different percentages (there is a tier 3 but it's out of scope), therefore the dax I am using is acting like a xlookup/vlookup, return only the first value it finds.

Table = 

SUMMARIZE(
    Template,
    Template[% of your annual time in 2023 dedicated to the activity],
    Template[Name],
    Template[Tier 1: Process Facet],
    Template[Tier 2: Activity Facet]
)

How can I make the sum to close to the 100%, let's say like when you do a sumifs?

Thanks

[v-kongfanf-msft · Accepted solution]

Hi @o59393 ,

Try to modify your formula like below: The ALLEXCEPT function is used to remove filters from all other columns except Name, Tier 1, and Tier 2, so that the calculation is performed in the context of these three columns only.

Table =
SUMMARIZE (
    Template,
    Template[% of your annual time in 2023 dedicated to the activity],
    Template[Name],
    Template[Tier 1: Process Facet],
    Template[Tier 2: Activity Facet],
    "TotalPercentage",
        CALCULATE (
            SUM ( Template[% of your annual time in 2023 dedicated to the activity] ),
            ALLEXCEPT (
                Template,
                Template[Name],
                Template[Tier 1: Process Facet],
                Template[Tier 2: Activity Facet]
            )
        )
)

Best Regards,
Adamk Kong

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

[o59393]

I also see that the concatenation of name, tier 1 and tier 2 has duplicate values.

I tried:

SUMX Percentage = 

DISTINCT(
SUMMARIZE(
    Template,
    Template[% of your annual time in 2023 dedicated to the activity],
    Template[Name],
    Template[Tier 1: Process Facet],
    Template[Tier 2: Activity Facet],
    Template[Merged.3]
))

But still shows me duplicated combinations.

Thanks.