cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Total at Parent level and % share of total of parent.

Hi All,

https://community.powerbi.com/t5/Desktop/Dynamic-Top-N-Categories-and-Top-N-Sub-Categories-and-Top-N...

In reference to my earlier question, I have further query related to same (sample data and PBI solution is there).

My issue here here is, now i need two new columns in one i need total of all cat/sub/brand at parent level irrespective to sum of  top one, and in second column i need  % share of top cat/sub/brand comparing it with the total of its parent.

1 ACCEPTED SOLUTION
Community Support

Hi @DeepDive ,

If I got it correctly, you can try these steps:

1. Create a level table manually like this, use it as a slicer:

2. Create a first measure named [Total] for each level selected by the slicer:

Total =
IF (
NOT ( ISFILTERED ( 'Level'[level] ) ),
IF (
HASONEFILTER ( 'Level'[level] ),
SWITCH (
SELECTEDVALUE ( 'Level'[level] ),
"Category", CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Category] ) ),
"Sub Category",
CALCULATE (
SUM ( 'Table'[Sales] ),
ALLEXCEPT ( 'Table', 'Table'[Sub Category] )
),
"Brand", CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Brand] ) ),
SUM ( 'Table'[Sales] )
),
)
)

3. Create another measure named [share %] for each level selected by the slicer:

% share =
IF (
NOT ( ISFILTERED ( 'Level'[level] ) ),
IF (
HASONEFILTER ( 'Level'[level] ),
VAR _Cat =
CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Category] ) )
VAR _Sub =
CALCULATE (
SUM ( 'Table'[Sales] ),
ALLEXCEPT ( 'Table', 'Table'[Sub Category] )
)
VAR _Brand =
CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Brand] ) )
RETURN
SWITCH (
SELECTEDVALUE ( 'Level'[level] ),
"Category", _Cat / _Cat,
"Sub Category", _Sub / _Cat,
"Brand", _Brand / _Cat
),
)
)

4. Use a table visual to show the result dynamically by the level slicer:

Best Regards,
Yingjie Li

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

2 REPLIES 2
Community Support

Hi @DeepDive ,

If I got it correctly, you can try these steps:

1. Create a level table manually like this, use it as a slicer:

2. Create a first measure named [Total] for each level selected by the slicer:

Total =
IF (
NOT ( ISFILTERED ( 'Level'[level] ) ),
IF (
HASONEFILTER ( 'Level'[level] ),
SWITCH (
SELECTEDVALUE ( 'Level'[level] ),
"Category", CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Category] ) ),
"Sub Category",
CALCULATE (
SUM ( 'Table'[Sales] ),
ALLEXCEPT ( 'Table', 'Table'[Sub Category] )
),
"Brand", CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Brand] ) ),
SUM ( 'Table'[Sales] )
),
)
)

3. Create another measure named [share %] for each level selected by the slicer:

% share =
IF (
NOT ( ISFILTERED ( 'Level'[level] ) ),
IF (
HASONEFILTER ( 'Level'[level] ),
VAR _Cat =
CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Category] ) )
VAR _Sub =
CALCULATE (
SUM ( 'Table'[Sales] ),
ALLEXCEPT ( 'Table', 'Table'[Sub Category] )
)
VAR _Brand =
CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Brand] ) )
RETURN
SWITCH (
SELECTEDVALUE ( 'Level'[level] ),
"Category", _Cat / _Cat,
"Sub Category", _Sub / _Cat,
"Brand", _Brand / _Cat
),
)
)

4. Use a table visual to show the result dynamically by the level slicer:

Best Regards,
Yingjie Li

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