Skip to main content
cancel
Showing results for
Search instead 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

Resolver II

## Total sales based on field parameter

Hi, Not sure if it is possible, but id like a column here which based on the parameter selected does a total of the matching grouping:

So in the case below, now that product group is selected, I need a colum which for A,D,G,J all show the total sales value for "Bike", which is the corresponding product group that they are connected to etc.
In case I was doing it by sub product group, the total for F and L for instance would show the total for "Bad coffe"

pbix file with data can be found here

1 ACCEPTED SOLUTION
Resolver II

Managed to solve it my self 🙂
Solution pbix

Created the following measure:

``````Sales by selected parameter =
VAR _SelectedParameter =
SELECTEDVALUE ( Parameter[Parameter Order] )
VAR _CurrentDimension =
SWITCH (
_SelectedParameter,
0, SELECTEDVALUE ( DimArticle[Product group] ),
1, SELECTEDVALUE ( DimArticle[sub product group] ),
2, SELECTEDVALUE ( 'Fact Sales'[Customer] )
)
VAR _TotalSales =
SWITCH (
_SelectedParameter,
0,
SUMX (
FILTER (
ALL('Fact Sales'),
RELATED ( DimArticle[Product group] ) = _CurrentDimension
),
'Fact Sales'[Sales Value]
),
1,
SUMX (
FILTER (
ALL('Fact Sales'),
RELATED ( DimArticle[sub product group] ) = _CurrentDimension
),
'Fact Sales'[Sales Value]
),
2,
SUMX (
FILTER (
ALL('Fact Sales'),
'Fact Sales'[Customer] = _CurrentDimension
),
'Fact Sales'[Sales Value]
)
)
RETURN
_TotalSales
``````

1 REPLY 1
Resolver II

Managed to solve it my self 🙂
Solution pbix

Created the following measure:

``````Sales by selected parameter =
VAR _SelectedParameter =
SELECTEDVALUE ( Parameter[Parameter Order] )
VAR _CurrentDimension =
SWITCH (
_SelectedParameter,
0, SELECTEDVALUE ( DimArticle[Product group] ),
1, SELECTEDVALUE ( DimArticle[sub product group] ),
2, SELECTEDVALUE ( 'Fact Sales'[Customer] )
)
VAR _TotalSales =
SWITCH (
_SelectedParameter,
0,
SUMX (
FILTER (
ALL('Fact Sales'),
RELATED ( DimArticle[Product group] ) = _CurrentDimension
),
'Fact Sales'[Sales Value]
),
1,
SUMX (
FILTER (
ALL('Fact Sales'),
RELATED ( DimArticle[sub product group] ) = _CurrentDimension
),
'Fact Sales'[Sales Value]
),
2,
SUMX (
FILTER (
ALL('Fact Sales'),
'Fact Sales'[Customer] = _CurrentDimension
),
'Fact Sales'[Sales Value]
)
)
RETURN
_TotalSales
``````

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors