cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## SUM By GROUP by Slicer

Hi Guys, I have data like this:

 Company Category Sub-Category Product Name sales ACOMP FOOD 1 A 1000 ACOMP FOOD 1 B 1500 ACOMP FOOD 1 C 2000 ACOMP FOOD 2 D 3000 ACOMP RETAIL 2 E 2000 ACOMP RETAIL 2 F 1000 BCOMP RETAIL 3 G 1000 BCOMP RETAIL 3 H 5000 BCOMP SERVICE 4 I 2500 BCOMP SERVICE 4 J 1000

The case when I put the slicer based on Product name, the value also keep SUM of Product Name.

My Expectation result, when I put the slicer based on Product Name can show SUM of Group in a Card Visual, looks like:

Sorry for my explanation in Excel mode, that's to make it clear enough.

Does someone know how to this calculation in Dax

BR

1 ACCEPTED SOLUTION
Community Support

Hi @tian

I think you need to create an unrelated Product Name table. Then build a slicer by this table and create measures.

``Product Name = VALUES('Table'[Product Name])``

Measure codes:

``````% Sub-Category =
VAR _SelectProductName =
SELECTEDVALUE ( 'Product Name'[Product Name] )
VAR _SubCategory =
CALCULATE (
MAX ( 'Table'[Sub-Category] ),
FILTER ( ALL ( 'Table' ), 'Table'[Product Name] = _SelectProductName )
)
VAR _SalesbyGroup =
CALCULATE (
SUM ( 'Table'[sales] ),
FILTER ( ALL ( 'Table' ), 'Table'[Sub-Category] = _SubCategory )
)
VAR _Total =
CALCULATE ( SUM ( 'Table'[sales] ), ALL ( 'Table' ) )
RETURN
DIVIDE ( _SalesbyGroup, _Total )``````
``````% Category =
VAR _SelectProductName =
SELECTEDVALUE ( 'Product Name'[Product Name] )
VAR _Category =
CALCULATE (
MAX ( 'Table'[Category] ),
FILTER ( ALL ( 'Table' ), 'Table'[Product Name] = _SelectProductName )
)
VAR _SalesbyGroup =
CALCULATE (
SUM ( 'Table'[sales] ),
FILTER ( ALL ( 'Table' ), 'Table'[Category] = _Category )
)
VAR _Total =
CALCULATE ( SUM ( 'Table'[sales] ), ALL ( 'Table' ) )
RETURN
DIVIDE ( _SalesbyGroup, _Total )``````
``````% Company =
VAR _SelectProductName =
SELECTEDVALUE ( 'Product Name'[Product Name] )
VAR _Company =
CALCULATE (
MAX ( 'Table'[Company] ),
FILTER ( ALL ( 'Table' ), 'Table'[Product Name] = _SelectProductName )
)
VAR _SalesbyGroup =
CALCULATE (
SUM ( 'Table'[sales] ),
FILTER ( ALL ( 'Table' ), 'Table'[Company] = _Company )
)
VAR _Total =
CALCULATE ( SUM ( 'Table'[sales] ), ALL ( 'Table' ) )
RETURN
DIVIDE ( _SalesbyGroup, _Total )``````

Result is as below.

Best Regards,
Rico Zhou

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 @tian

I think you need to create an unrelated Product Name table. Then build a slicer by this table and create measures.

``Product Name = VALUES('Table'[Product Name])``

Measure codes:

``````% Sub-Category =
VAR _SelectProductName =
SELECTEDVALUE ( 'Product Name'[Product Name] )
VAR _SubCategory =
CALCULATE (
MAX ( 'Table'[Sub-Category] ),
FILTER ( ALL ( 'Table' ), 'Table'[Product Name] = _SelectProductName )
)
VAR _SalesbyGroup =
CALCULATE (
SUM ( 'Table'[sales] ),
FILTER ( ALL ( 'Table' ), 'Table'[Sub-Category] = _SubCategory )
)
VAR _Total =
CALCULATE ( SUM ( 'Table'[sales] ), ALL ( 'Table' ) )
RETURN
DIVIDE ( _SalesbyGroup, _Total )``````
``````% Category =
VAR _SelectProductName =
SELECTEDVALUE ( 'Product Name'[Product Name] )
VAR _Category =
CALCULATE (
MAX ( 'Table'[Category] ),
FILTER ( ALL ( 'Table' ), 'Table'[Product Name] = _SelectProductName )
)
VAR _SalesbyGroup =
CALCULATE (
SUM ( 'Table'[sales] ),
FILTER ( ALL ( 'Table' ), 'Table'[Category] = _Category )
)
VAR _Total =
CALCULATE ( SUM ( 'Table'[sales] ), ALL ( 'Table' ) )
RETURN
DIVIDE ( _SalesbyGroup, _Total )``````
``````% Company =
VAR _SelectProductName =
SELECTEDVALUE ( 'Product Name'[Product Name] )
VAR _Company =
CALCULATE (
MAX ( 'Table'[Company] ),
FILTER ( ALL ( 'Table' ), 'Table'[Product Name] = _SelectProductName )
)
VAR _SalesbyGroup =
CALCULATE (
SUM ( 'Table'[sales] ),
FILTER ( ALL ( 'Table' ), 'Table'[Company] = _Company )
)
VAR _Total =
CALCULATE ( SUM ( 'Table'[sales] ), ALL ( 'Table' ) )
RETURN
DIVIDE ( _SalesbyGroup, _Total )``````

Result is as below.

Best Regards,
Rico Zhou

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

Super User

@tian , based on what I got

measure = divide(sum(Table[Sales]), calculate(sum(Table[Sales]), all(Table)))

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.