Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am working with data that is structured in a parent-child relationship which up to 12-levels. Every level already has its own value as per their ID. My objective is to display the value that is shown at every level and not roll up the sum between parent and child as I need the parent-child relationship in PowerBI for drill through purposes. The blank value in Level 3 also the child of Level 2 (which has the same ID) .
As for my DIM_INDICATOR table has flat hierarchy which only have ID and PARENTID. I have created the hierarchy using PATH functions. Attached is the sample dataset that's similar to my current project.
This content is similar to my case
https://community.powerbi.com/t5/Desktop/Do-not-sum-certain-columns-in-matrix-Show-values-based-on-f...
I tried to do the same approach but still not get the right outcome. Maybe because I'm using PATH function and not suitable to use the approach.
Sample dataset
LEVEL 1 | LEVEL 2 | LEVEL 3 | VALUE |
VEHICLE | 2345 | ||
BUS | 567 | ||
BUS-A | 78 | ||
BUS-B | 56 | ||
CAR | 456 | ||
CAR-A | 234 | ||
CAR-B | 33 | ||
CAR-C | 123 | ||
LORRY | 765 | ||
LORRY-A | 343 | ||
LORRY-B | 122 | ||
LORRY-C | 5 | ||
PLANE | 123 | ||
PLANE-A | 45 | ||
PLANE-B | 34 | ||
PLANE-C | 21 |
Sample DIM_KPI
DIM_KPI_ID | ID | PARENTID | VEHICLE_NAME |
1 | 100 | VEHICLE | |
2 | 101 | 100 | BUS |
3 | 102 | 100 | CAR |
4 | 103 | 100 | LORRY |
5 | 104 | 100 | PLANE |
6 | 105 | 101 | BUS |
7 | 106 | 101 | BUS-A |
8 | 107 | 101 | BUS-B |
9 | 108 | 102 | CAR |
10 | 109 | 102 | CAR-A |
11 | 110 | 102 | CAR-B |
12 | 111 | 102 | CAR-C |
13 | 112 | 103 | LORRY |
14 | 113 | 103 | LORRY-A |
15 | 114 | 103 | LORRY-B |
16 | 115 | 103 | LORRY-C |
17 | 116 | 104 | PLANE |
18 | 117 | 104 | PLANE-A |
19 | 118 | 104 | PLANE-B |
20 | 119 | 104 | PLANE-C |
Thank you.
Hi Ashish,
I have both ID and ParentID present in a dimension table called DIM_KPI. The value is taken from fact table called FACT_VEHICLE. In fact table only have the DIM_KPI_ID, DIM_MONTH and the value. Both tables connected with DIM_KPI_ID.
I created the hierarchy using PATH function. By creating the hierarchy, the PARENT is showing at CHILD level too with the same ID. For example, CAR has ID and PARENTID as CAR is the PARENT for CAR-A, CAR-B and CAR-C. But in my case, CAR also appear as blank with value at child row. Something like the picture below.
The parent value should be the same as the blank row in child as it has the same ID. When collapse the row, the value showing 155,963.56 not summing up the child.
I created calculated column as below but not give me the desired result.
Is my explanation clear?
Thank you.
Hi,
Are ID and Parent ID already columns present in your source data table - can thy be taken as given? How did you get the numbers in the output table?
Did you ever end up figuring this out? I've also run into the same issue.
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
39 | |
25 | |
21 |