cancel
Showing results for
Did you mean:  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.  Super User Announcements #### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world. #### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st! #### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better. #### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison! Top Solution Authors
Top Kudoed Authors
Users online (3,391)