Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
KriZo
Resolver I
Resolver I

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

 

KriZo_0-1688047909197.png

 

1 ACCEPTED SOLUTION
KriZo
Resolver I
Resolver I

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 

 

View solution in original post

1 REPLY 1
KriZo
Resolver I
Resolver I

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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