topic Re: Calculate Region revenue in a Country table in DAX Commands and Tips

Calculate Region revenue in a Country table

Anonymous — Thu, 15 Oct 2020 12:57:48 GMT

I require the Dax to be able to calculate the region revenue instead of the country revenue in a matrix table by data set is as follows

the table i'm trying to create in power BI is something like this where i display both the country Rev and the Region rev next to each other which i have managed to do with the following dax.

Region Rev = CALCULATE(SUM('Dataset 1'[Revenue]),FILTER(FILTER(ALL('Dataset 1'),'Dataset 1'[Region]=SELECTEDVALUE('Dataset 1'[Region])),'Dataset 1'[Year]=SELECTEDVALUE('Dataset 1'[Year])))

However i cannot get it to calculate by industry and it is just giving me the total rev for the region, rather then by Industry by for that region.

in theory all i want to do is stop the measure from filtering on Country and instead filter on the region for that country when it is displaying any values

any help would be much appreciated!

Re: Calculate Region revenue in a Country table

Anonymous — Fri, 16 Oct 2020 12:03:29 GMT

Please try to learn more about DAX since the formula you've shown tells me immediately you should invest quite a bit of time in learning DAX and in data modeling. There are many free and quite good resources on the net you could use. Many of them on YT as well.

I think this is what you're after:

[Region Total Rev] =
CALCULATE(
SUM( T[Revenue] ),
REMOVEFILTERS( T[Country] )
)

Re: Calculate Region revenue in a Country table

Anonymous — Wed, 21 Oct 2020 07:29:55 GMT

Hi Daxer

this would not work am im trying to calculate the region revenue when a country is filtered. simply removing the country filter is not what is required.

when a country is selected it needs to show what the total revenue is for the region related to that country. I also need this to be able calculate for products and industry i.e showing the total region revenue for an industry/ product when a country is selected.

Does anyone else have any ideas how to do this?

Re: Calculate Region revenue in a Country table

wdx223_Daniel — Wed, 21 Oct 2020 08:41:33 GMT

Anonymous does the code as below help you?

Region Rev = CALCULATE(SUM('Dataset 1'[Revenue]),'Dataset 1'[Region] IN VALUES('Dataset 1'[Region]),ALL('Dataset 1'[Country]))

Re: Calculate Region revenue in a Country table

Anonymous — Wed, 21 Oct 2020 09:26:36 GMT

wdx223_Daniel thanks this works perfectly! Thanks alot

Sorry i can't access my Anonymous account at the moment so had to create this new one, unfortunately I cannot mark as solution

Re: Calculate Region revenue in a Country table

Anonymous — Wed, 21 Oct 2020 09:39:23 GMT

Interesting... Here are your own words: "[...] in theory all i want to do is stop the measure from filtering on Country and instead filter on the region for that country when it is displaying any values"

My code does exactly what you requested.