Forum Discussion

dariog's avatar
dariog
Regular Visitor
5 years ago
Solved

Calculation based on filter selection

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!

Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.