Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Show sum in table column based on condition

Hello, I have the following table in Power BI and i am using a slicer for the "Region" column. If i select Region = YUN, then the sum in the "Total" column is correct, since the currencies are the...
  • PaulDBrown's avatar
    3 years ago

    Here is one way.

    First the model:

    Include slicers from the dimension tables and use the dimension table fields in the visual. Next create this measure:

     

    Filter Slicers = 
    COUNTROWS(RELATEDTABLE(fTable))

     

    Add this measure to the filter pane for each slicer and set the value to greater or equal to 1. This will filter each slicer to show the complementary values for each field only.

    Now create the measure to show the total only when one currencies is displayed (if there are two or more currencias, the rows are displayed but not the total).

     

    Filter Sum =
    VAR _Rows =
        IF (
            ISBLANK ( [Sum Total] ),
            BLANK (),
            CALCULATE (
                DISTINCTCOUNT ( 'fTable'[Currency] ),
                ALLEXCEPT ( fTable, 'Region Table'[dRegion] )
            )
        ) //Calculates the distincount of currencies by region
    RETURN
        IF (
            ISFILTERED ( 'Currency Tbale'[dCurrency] ),
            [Sum Total],
            //if a currency is selected, the sum is returned
            IF (
                _Rows > 1,
                IF ( ISINSCOPE ( 'Region Table'[dRegion] ), [Sum Total] ) // will not return a total if a currency is not selected and there are more than 1 currencies by region
                ,
                [Sum Total]
            )
        )
    //will return the sum if a region is selected with only 1 currency.
    

     

    and you will get:

     

     

    Sample PBIX file attached

  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    For that you need this measure:

     

    Sum converted to euros if =
    VAR _Rows =
        IF (
            ISBLANK ( [Sum Total] ),
            BLANK (),
            CALCULATE (
                DISTINCTCOUNT ( 'fTable'[Currency] ),
                ALLEXCEPT ( fTable, 'Region Table'[dRegion] )
            )
        )
    VAR _NumCurr =
        CALCULATE (
            DISTINCTCOUNT ( 'Currency Tbale'[dCurrency] ),
            ALLSELECTED ( 'Currency Tbale'[dCurrency] )
        )
    RETURN
        IF ( _NumCurr = 1, [Sum Total], IF ( _Rows > 1, [Sum Euro], [Sum Total] ) )
    

     

    (I've added an extra row with the region "Narnia" in USD to check the measure if more than one region is selected)

    I have also altered the [Filter Sum] measure for the original table to account for the posibility of the selection of more than one currency:

     

     

     

    Filter Sum =
    VAR _Rows =
        IF (
            ISBLANK ( [Sum Total] ),
            BLANK (),
            CALCULATE (
                DISTINCTCOUNT ( 'fTable'[Currency] ),
                ALLEXCEPT ( fTable, 'Region Table'[dRegion] )
            )
        )
    VAR _NumCurr =
        CALCULATE (
            DISTINCTCOUNT ( fTable[Currency] ),
            ALLSELECTED ( 'Currency Tbale'[dCurrency] )
        )
    RETURN
        IF (
            AND ( NOT ( ISINSCOPE ( 'Region Table'[dRegion] ) ), _NumCurr <> 1 ),
            BLANK (),
            IF (
                ISFILTERED ( 'Currency Tbale'[dCurrency] ),
                [Sum Total],
                IF (
                    _Rows > 1,
                    IF ( ISINSCOPE ( 'Region Table'[dRegion] ), [Sum Total] ),
                    [Sum Total]
                )
            )
        )
    

     

     

     

     

     

     

    New file attached

    (PS, if this works for you, please mark this post as the solution instead of the previous one, since this one covers the possibility of multiple currency selections for both the clustered column and table visuals!)

  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    Ok. So thanks to the new requirements, I've come up with a much simpler solution to the measures needed:

    Filter Sum =
    VAR _Rows =
        CALCULATE (
            DISTINCTCOUNT ( fTable[Currency] ),
            ALLSELECTED ( 'Calendar'[Date] )
        )
    RETURN
        IF ( _rows > 1, BLANK (), [Sum Total] )
    
    Sum converted to euros if =
    VAR _Rows =
        CALCULATE (
            DISTINCTCOUNT ( fTable[Currency] ),
            ALLSELECTED ( 'Calendar'[Date] ),
            ALLSELECTED ( fTable )
        )
    RETURN
        IF ( _Rows = 1, [Sum Total], [Sum Euro] )
    

    and consequently simplified all the other Conditional Formatting and title measures.

    New file attached