Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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!
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
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
183 | |
111 | |
105 | |
77 | |
70 |