DUPLICATE Active customer per Revenue Group Recognition and Ragged Hierarchy Percentage

[annnvy21]

Hi Power BI community,
I have one case that Revenue is formed by 4 revenue groups, active customer per group:
Revenue Group A: 200 active customers
Revenue Group B: 185 active customers
Revenue Group C: 205 active customers
Revenue Group 😧 389 active customers
But Total Revenue includes only 528 active customers because one customer can use multiple revenue groups.
I want to display the matrix table in power bi that presents (Total Group is a subcategory of 4 revenue groups above)
Total Group: 495 active customers
Revenue Group A: 200 active customers
Revenue Group B: 185 active customers
Revenue Group C: 205 active customers
Revenue Group 😧 389 active customers
Can it work?

If it works, then can I process this advanced case?
I want to calculate the Adoption rate monthly like the attachment below (The adoption rate is calculated by each detail category's active customers divided by the Total active customer.)

Many thanks,
Vy

[annnvy21]

Hi @v-yanjiang-msft ,
I have a parent-child hierarchy table like this, all values are attached to a 4-tier work breakdown structure Revenue -> Mapping lv1 -> Mapping lv2 -> Mapping lv3. Does it work in a parent-child hierarchy matrix? Because my project has lots of detail products, I use parent-child hierarchy and flexible drill-through which Parent product to analyze. When showing your solution in a matrix, it's gonna be a long list and can not filter each Parent product.

Revenue	Mapping lv1	Mapping lv2	Mapping lv3
A	a1
A	a2
A	a2
B	b1	b1.1
B	b1	b1.2	b1.2.1
B	b1	b1.2	b1.2.2
B	b2	b2.1
B	b2	b2.2
C	c1	c1.1
C	c1	c1.1
C	c2	c2.1
C	c2	c2.2	c2.2.1
C	c2	c2.2	c2.2.2
C	c2	c2.3
C	c2	c2.4
D	d1

Anyway, thanks for your solution!

[v-yanjiang-msft]

Hi @annnvy21 ,

If there's a hierarchy in your sample, the value of A should be sum of a1,a2 and a3, but it isn't in your data.

Best regards,

Community Support Team_yanjiang

[v-yanjiang-msft]

Hi @annnvy21 ,

According to your description, here's my sample data:

TOTAL table:

Here's my solution:

1.Create a new table:

2.Create a new measure:

Measure =
SWITCH (
    MAX ( 'Column'[Column] ),
    "Active customer", MAX ( 'Table'[Active customer] ),
    "Adoption rate",
        FORMAT (
            DIVIDE (
                MAX ( 'Table'[Active customer] ),
                MAXX (
                    FILTER ( 'TOTAL', 'TOTAL'[Date] = MAX ( 'Table'[Date] ) ),
                    'TOTAL'[TOTAL ACTIVE CUSTOMER]
                )
            ),
            "0.00%"
        )
)

In a matrix,

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

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