Forum Discussion

Rai_BI's avatar
Rai_BI
Helper IV
2 years ago
Solved

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

 Please download the PBIX file here

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

    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. 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    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.