cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RedGC
Frequent Visitor

Total Sales in Matrix table based on some conditions.

Hello,

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

 

RedGC_0-1620062169631.png

  

RedGC_1-1620062206369.png

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 : 

RedGC_3-1620062652601.png

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

 

Thanks alot for your help.

 

 

 

 

 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

Hi, @RedGC 

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.

 

Picture3.png

 

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)
 
 
 

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

 

View solution in original post

2 REPLIES 2
Jihwan_Kim
Community Champion
Community Champion

Hi, @RedGC 

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.

 

Picture3.png

 

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)
 
 
 

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

 

View solution in original post

RedGC
Frequent Visitor

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors