Calculation based on filter selection

[dariog]

Hello Everyone,

I have a problem. I have a table like the following:

Location     Sales

North          10

South           11

West             9

East              15

I have a page filter based on the following 5 voices: Country (the sum of all 4 other voices), North, South, West, East.

Obviously if I use the filter on Country, my custom formula returns me a blank value because there aren't Country rows in my table. And I can't simply select the other 4 voices to make the total because it creates issues on other visuals.

I'd need a formula that says something like:

if the voice Country is selected in the filter, then make the sum of all rows, otherwise use the original formula.

thanks ini advance for any help!

[Anonymous · Accepted solution]

Hi @dariog ,

Create a separate table by entering data

This is the measure I created

Result = 
IF (
    SELECTEDVALUE ( 'Table (2)'[Category] ) = "Country",
    CALCULATE ( SUM ( 'Table'[Sales] ), ALL ( 'Table' ) ),
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', [Location] IN ALLSELECTED ( 'Table (2)'[Category] ) )
    )
)

Country is selected

East and North are selected

You check details from the attachment.

Best Regards,

Stephen Tao

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

[Jihwan_Kim]

Hi, @dariog 

Please correct me if I wrongly understood your question.

I think you can try to use something like,

 if ( ISFILTERED(countrycolumn), sumofallrows, original formula)

If it is OK with you, please share your sample pbix file, then I can try to look into it to create a more accurate measure.

thank you.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.