Total Sales in Matrix table based on some conditions.

[Anonymous]

Hello,

I have a sales table with the following tables (exist the attached pbi file) : 

There's no relationship between sales table and above tables. 

I need to display in Matrix table ( Range int Rows and GM Range in Column) to show Total Sales for those sales that are inside a range ( ex : $7M - $9M) AND in specific GM range (Gross Margin). As a result I should have the following format : 

Here is the Pbi file with Demo Data : https://www.dropbox.com/s/yt6c8sktx3nwzh2/DemoPBI.pbix?dl=0

Thanks alot for your help.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

Two measures are created, and I inserted the second measure into the visualization.

Total Sales by Customer by Sales Range =

CALCULATE (

Sales[TotalSales],

FILTER (

VALUES ( Sales[Customer] ),

COUNTROWS (

FILTER (

'Sales Range',

[TotalSales] > 'Sales Range'[Min]

&& [TotalSales] <= 'Sales Range'[Max]

)

) > 0

)

)

Total Sales by Customer by salesrange & gmrange =

COALESCE(CALCULATE (

[Total Sales by Customer by Sales Range],

FILTER (

VALUES ( Sales[Customer] ),

COUNTROWS (

FILTER ( 'GM Range', [GM] > 'GM Range'[Min] && [GM] <= 'GM Range'[Max] )

) > 0

)

),0)

https://www.dropbox.com/s/re78s9p6jw6phaf/DemoPBI.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Thanks alot for your help @Jihwan_Kim, your answer worked very good !.
Thanks,