SUM a column with multiple filters from another column of the same table

[JasonNahal]

Hi DAX Gurus and fellow Noobs,

 I am looking for help with the following Table:

ReferenceName	Amount
GST	-40
Call out fee	100
reimbursement	400
GST	-200
Call out fee	60
GST	20
Call out fee	100
Meals	50

I want to get the SUM(Table[Amount]) where Table[ReferenceName] = "GST" AND "Call out fee". What DAX formula could achieve this?

The above table is an extract from a SQL db and has 1000s of rows and 100s of different values in the ReferenceName column.

Any help will be much appriciated. thank you!

[MFelix · Accepted solution]

Hi @JasonNahal,

Depending on the way you setup your visialization you don't even need a measure to calculate this.

You can place the values in a visual and then filter the visu by the reference column. Other option is to use the reference column on the legend's of the charts that will give you a column line or bar for each of the references.

If you really want to use a measure you need to use something similar to this:

Gst = CALCULATE(SUM( TABLE[AMOUNT]) ; TABLE[REFERENCENCENAME] = "Gst")

Hope this helps.

[v-yiruan-msft · Accepted solution]

Hi @JasonNahal ,

You can directly use some default visuals in Power BI Desktop and add some visual-level filters to achieve this function (for example: table visual)

In addition , if you want to create calculated column/ measure through DAX , you can use the below similar formula to create it .

 

If the above methods are not applicable in your scenario, please provide the related table structure and more sample data(exclude sensitive data).

Best Regards

Rena

[JasonNahal]

Thank you @v-yiruan-msft. Filters did the trick. 

[MFelix · Accepted solution]

Hi @JasonNahal,

Depending on the way you setup your visialization you don't even need a measure to calculate this.

You can place the values in a visual and then filter the visu by the reference column. Other option is to use the reference column on the legend's of the charts that will give you a column line or bar for each of the references.

If you really want to use a measure you need to use something similar to this:

Gst = CALCULATE(SUM( TABLE[AMOUNT]) ; TABLE[REFERENCENCENAME] = "Gst")

Hope this helps.

[JasonNahal]

thank you for responding @MFelix. using Filters for the visualization did the trick.