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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Rai_BI
Helper III
Helper III

Dynamic summation

Hello friends,
Can anyone help me solve this problem?

In Power BI, I have a table with two columns. The 'Salesperson 1' column and the 'Salesperson 2' column.


I want to create three table visuals where visual 1 will have the Salesperson 1 column in context. In visual 2 you will have the Seller 2 column in context and in visual 3 you will have the Seller 1 and Seller 2 column in context.


I need to create a single unique measure for the three visual elements in which if the context contains the Salesperson 1 column then return the measure [Goal 1], otherwise, if the context contains the Salesperson 2 column then return the measure [Goal 2], otherwise, if the context contains the Salesperson 1 column and also the Salesperson 2 column, then return the sum of the measures [Goal 1]+[Goal 2].

I did the following DAX measurement below, I am not getting the desired result as the total grid value is returning BLANK. I don't understand because I'm using the SUMX function to force the value to appear.

Measure Test = 

VAR _Seller1inScope = HASONEFILTER('customer table'[Name Seller1])
VAR _Seller2inScope = HASONEFILTER('customer table'[Name Seller2])
VAR _Swith = 
    SWITCH(
        TRUE(),
        _Seller1inScope && NOT _Seller2inScope,
        SUMX(
            VALUES('customer table'[Name Seller1]),[Goal Seller 1]),
        
        _Seller2inScope && NOT _Seller1inScope,
        SUMX(
            VALUES('customer table'[Name Seller2]),[Goal Seller 2]),
                
        _Seller1inScope && _Seller2inScope,
        [Seller 1 + Seller 2]
        
    )


RETURN
_Swith

Sem título.png

 Please download the PBIX file here

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

Hi @Rai_BI ,

 

We can create a measure.

 

Measure 4 = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Slicer'[Seller]) = "Name Seller1", SUMX ( VALUES ( 'customer table'[Name Seller1] ), [Measure Test] ),
    SELECTEDVALUE ( 'Slicer'[Seller]) = "Name Seller2",SUMX ( VALUES ( 'customer table'[Name Seller2] ), [Measure Test] ),
    SUMX(SUMMARIZE('customer table','customer table'[Name Seller1],'customer table'[Name Seller2],"measure",[Measure Test]),[measure])
)

 

We can create a slicer table.

 

Slicer = DATATABLE ( 
    "Seller", STRING, 
    {
        { "Name Seller1" },
        { "Name Seller2" }
        
    }
) 

 

Edit interactions for each slicer in the format pane and hide the slicers.

vtangjiemsft_0-1707363079181.pngvtangjiemsft_1-1707363097964.png

If you don't want to use a slicer, you can refer to this post to create a field parameter.

 

Best Regards,

Neeko Tang

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

View solution in original post

1 REPLY 1
v-tangjie-msft
Community Support
Community Support

Hi @Rai_BI ,

 

We can create a measure.

 

Measure 4 = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Slicer'[Seller]) = "Name Seller1", SUMX ( VALUES ( 'customer table'[Name Seller1] ), [Measure Test] ),
    SELECTEDVALUE ( 'Slicer'[Seller]) = "Name Seller2",SUMX ( VALUES ( 'customer table'[Name Seller2] ), [Measure Test] ),
    SUMX(SUMMARIZE('customer table','customer table'[Name Seller1],'customer table'[Name Seller2],"measure",[Measure Test]),[measure])
)

 

We can create a slicer table.

 

Slicer = DATATABLE ( 
    "Seller", STRING, 
    {
        { "Name Seller1" },
        { "Name Seller2" }
        
    }
) 

 

Edit interactions for each slicer in the format pane and hide the slicers.

vtangjiemsft_0-1707363079181.pngvtangjiemsft_1-1707363097964.png

If you don't want to use a slicer, you can refer to this post to create a field parameter.

 

Best Regards,

Neeko Tang

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

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.

Top Solution Authors