Add Total using DAX

[Anonymous]

Hi All,

I have three tables as shown in the below screenshot:

The Filter Condition column will be placed as a filter in the report. Based on the filter selection, the result should be as follows:

I can get values for the Capitals but I am not able to add the total for the rows highlighted in green. (Asia and Outside Asia). I have used DAX. May I know what DAX should i use to add the values.

[Ashish_Mathur]

Hi,

Ideally you should not have the Filter condition column in Table1.  The column in that table instead should be Continent with only 2 entries under that column - Asia and Outside Asia.  Create a relationship (Many to One and Single) from Table3 to Table2.  In Table2, write this calculated column formula

Continent = lookupvalue('Table1'[continent],'Table1'[Places],'Table2'[Places])

Create a Continent slicer from Table2.  Write this measure

Measure = sum('Table3'[value])

Hope this helps.

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description, you want to add a total row in your table visual.Here are the steps you can refer to:
(1)My test data is the same as yours.

The table relationship is that:

(2)Due to we have no right row headers , we need to click "New Table" to create a table as row headers in table visual:

Row headers = UNION( EXCEPT( ALL('Table 1'[Places]) ,  ALL('Table 2'[Places])) , DISTINCT('Table 2'[Capital])  )

(3)Then we can create a measure :

Measure = var _places = VALUES('Table 1'[Places])
var _capitals = VALUES('Table 3'[Capitals])
var _cur = MAX('Row headers'[Places])
var _total = CALCULATE( SUM('Table 3'[Value]))
var _sum = CALCULATE( SUM('Table 3'[Value]), TREATAS({_cur},'Table 3'[Capitals]))
return
IF(_cur in _places , _total , IF(_cur in _capitals , _sum,BLANK()))

 (4)Then we can put the field we need on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Greg_Deckler]

@Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8