Show sum in table column based on condition

[Anonymous]

Hello,

I have the following table in Power BI and i am using a slicer for the "Region" column.

1. If i select Region = YUN, then the sum in the "Total" column is correct, since the currencies are the same, that is, USD.
2. If i select Region = TOD, then the sum in the "Total" column is not correct, since it is adding up two different currencies.

The problem is that the sum shown at the end of the "Total" column is meaningless if the currencies are not the same in all the rows. I am trying to display the sum in the "Total" column only if the currencies are the same, otherwise hide the sum value. Is this possible? Any help is much appreciated!

Document ID	Region	Total	Currency
3998	TOD	6485.52	NOK
4050	TOD	87889	USD
4050	WAP	89367.5	DKK
4060	HEP	27050.73	EUR
4139	YUN	4785.25	USD
4145	WAP	720	DKK
4149	HEP	583	EUR
4157	WAP	15204	CAD
4160	YUN	4197	USD
4160	HEP	44924	EUR
4164	WAP	1297.59	NOK

[PaulDBrown · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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

[Anonymous]

Hi @PaulDBrown Thanks for your reply! I have a related question about presenting the same data in a chart, so that:

1. if the currencies do not match, then show the Y-axis as "Converted to EUR", else

2. if the currencies are the same, then show the Y-axis as the common currency.

Is this possible? Any help is much appreciated!

Document ID	Region	Total	Currency	Converted to EUR
3998	TOD	6485.52	NOK	621.58
4050	TOD	87889	USD	90233.88
4050	WAP	89367.5	DKK	12014.28
4060	HEP	27050.73	EUR	27050.73
4139	YUN	4785.25	USD	4912.92
4145	WAP	720	DKK	96.79
4149	HEP	583	EUR	583
4157	WAP	15204	CAD	11356.44
4160	YUN	4197	USD	4308.98
4160	HEP	44924	EUR	44924
4164	WAP	1297.59	NOK	124.36

[PaulDBrown · Accepted solution]

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!)

[Anonymous]

@PaulDBrown Thank you for another brilliant solution!

By the way, how did you create the animated gif? It is an excellent way to show a quick demo!

[PaulDBrown]

I use an app called "screen to gif" which is free to download.

BTW, I edited the file and the [Filter Sum] measure while you were posting, so please check the current measure and download the file as it's posted now!

And please change the solution status to the last post -it corrects errors if there is more than one selection in the slicers!)

[Anonymous]

@PaulDBrown Thanks for updating the solution! I am going through the code to try to understand it better. Could you please identify which of those measures are relevant? I would like to clean up a little so that it is a bit easier to figure out.

From my understanding, the only measures which should be kept are:

1. Filter Sum
2. Sum Total
3. Sum converted to euros if
4. Title if euros

Is this correct?

[PaulDBrown]

Yes, sorry about the extra measures. Let me shed some light:

measures with "CF" are for conditional formatting. If they contain the word "Title" (either with CF or without), they are used for conditional formatting the title text. Apart from these, you can get rid of "TEST"

(Filter Slicers is important to filter the other slicer based on selections)

Sum Euro is just the sum of the Euro field

[Anonymous]

@PaulDBrown Thanks for the clarifications!

I have added 2 columns to the table: City and Registered Date.

Document ID	Region	City	Registered Date	Total	Currency	Euro conversion
3998	TOD	London	01/07/2021	6485.52	NOK	621.58
4050	TOD	London	09/11/2021	87889	USD	90233.88
4050	WAP	Saint Petersburg	11/12/2021	89367.5	DKK	12014.28
4060	HEP	Saint Petersburg	13/12/2021	27050.73	EUR	27050.73
4139	YUN	Madrid	05/01/2022	4785.25	USD	4912.92
4145	WAP	Madrid	06/01/2022	720	DKK	96.79
4149	HEP	Madrid	27/02/2022	583	EUR	583
4157	WAP	Paris	04/04/2022	15204	CAD	11356.44
4160	YUN	Paris	09/04/2022	4197	USD	4308.98
4160	HEP	Minsk	06/05/2022	44924	EUR	44924
4164	WAP	Moscow	07/06/2022	1297.59	NOK	124.36
5555	Narnia	Rome	09/09/2022	1234	USD	6666

I am trying to add slicers for Document ID, City and Registered Date. All 4 slicers work fine in the table. But the slicers (except for the Region slicer) do not give the correct results in the chart since the currencies are always converted to Euro, even when all the currencies listed are similar.

In the chart, i also grouped the data by Registered Date and used a Calendar table to show them by month-year.

The Region slicer is set and the chart below displays correctly.

The Document ID slicer is set and the chart below should display: Currency in DKK.

The City slicer is set and the chart below should display: Currency in USD.

The Registered Date slicer is set and the chart below should display: Currency in USD.

The model:

Any idea what could be wrong and how to fix it?

Here is the modified PBIX file: https://drive.google.com/file/d/18wvj98K8xjtsdrRKYwQgNoCghOck2grn/view?usp=sharing

[PaulDBrown · Accepted solution]

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

[Anonymous]

@PaulDBrown Thanks for the updated and simplified solution!

Maybe i'm wrong but it seems like the dimension tables are no longer needed?

I have changed the selected Fields for the table and it does not seem to make any difference:

I have also updated the slicers (Region, City and Document ID) to use the Fields from the fTable.

But before i delete the dimension tables, i would appreciate your confirmation - just in case something goes wrong if more data is added in the table.

[PaulDBrown]

It is a best practice and very highly recommended to use dimension tables!

Personally I would not delete them, and use them as the fields for measures, filters, slicers etc...

[Dinesh_Suranga]

@Anonymous 

Hi,

create a measure with SUMX to get the total.

thank you.