Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Calculate Region revenue in a Country table

Power BI table.PNGDummy Dataset.PNGHi 

 

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!

 

 

 

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

@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]))

View solution in original post

5 REPLIES 5
wdx223_Daniel
Super User
Super User

@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]))
Anonymous
Not applicable

@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

Anonymous
Not applicable

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] )
)
Anonymous
Not applicable

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?

Anonymous
Not applicable

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors