Forum Discussion
Show sum in table column based on condition
- 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
- 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!)
- 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
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!
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!)
- Anonymous3 years agoNot applicable
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:
- Filter Sum
- Sum Total
- Sum converted to euros if
- Title if euros
Is this correct?
- PaulDBrown3 years agoCommunity Champion
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
- Anonymous3 years agoNot applicable
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