Power BI - Overall calculation for a matrix

[Anonymous]

Hello All,

We have a matrix visual which shows region wise sales information. Now we need to add a Overall column which should be sum of the regions with some conditions. Below is the detail:

How to achieve it.

Thanks,

PBI V2

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Hello @Greg_Deckler ,

Thanks for your response.

Below is the sample data that we are using for Reporting:

Month	Region	Sales Amount	Status_Color
Dec'23	America	277833	Blue
Dec'23	America	277833	Blue
Dec'23	America	277833	Blue
Jan'24	America	216333	Green
Jan'24	America	216333	Green
Jan'24	America	216333	Blue
Nov'23	America	277833	Green
Nov'23	America	277833	Blue
Nov'23	America	277833	Green
Nov'23	America	277833	Blue
Nov'23	America	277833	Orange
Oct'23	America	277833	Blue
Oct'23	America	277833	Green
Oct'23	America	277833	Blue
Oct'23	America	277833	Blue
Oct'23	America	277833	Orange
Dec'23	Asia Pacific	100572	Blue
Dec'23	Asia Pacific	100572	Blue
Dec'23	Asia Pacific	100572	Blue
Jan'24	Asia Pacific	91700	Green
Jan'24	Asia Pacific	91700	Green
Jan'24	Asia Pacific	91700	Blue
Nov'23	Asia Pacific	100572	Green
Nov'23	Asia Pacific	100572	Blue
Nov'23	Asia Pacific	100572	Green
Nov'23	Asia Pacific	100572	Blue
Nov'23	Asia Pacific	100572	Orange
Oct'23	Asia Pacific	100572	Blue
Oct'23	Asia Pacific	100572	Green
Oct'23	Asia Pacific	100572	Blue
Oct'23	Asia Pacific	100572	Blue
Oct'23	Asia Pacific	100572	Orange
Dec'23	EMEA	123564	Blue
Dec'23	EMEA	123564	Blue
Dec'23	EMEA	123564	Blue
Jan'24	EMEA	116380	Green
Jan'24	EMEA	116380	Green
Jan'24	EMEA	116380	Blue
Nov'23	EMEA	123564	Green
Nov'23	EMEA	123564	Blue
Nov'23	EMEA	123564	Green
Nov'23	EMEA	123564	Blue
Nov'23	EMEA	123564	Orange
Oct'23	EMEA	123564	Blue
Oct'23	EMEA	123564	Green
Oct'23	EMEA	123564	Blue
Oct'23	EMEA	123564	Blue
Oct'23	EMEA	123564	Orange
Dec'23	Europe	212378	Blue
Dec'23	Europe	212378	Blue
Dec'23	Europe	212378	Blue
Jan'24	Europe	197720	Green
Jan'24	Europe	197720	Green
Jan'24	Europe	197720	Blue
Nov'23	Europe	212378	Green
Nov'23	Europe	212378	Blue
Nov'23	Europe	212378	Green
Nov'23	Europe	212378	Blue
Nov'23	Europe	212378	Orange
Oct'23	Europe	212378	Blue
Oct'23	Europe	212378	Green
Oct'23	Europe	212378	Blue
Oct'23	Europe	212378	Blue
Oct'23	Europe	212378	Orange
Dec'23	Oceania	185450	Blue
Dec'23	Oceania	185450	Blue
Dec'23	Oceania	185450	Blue
Jan'24	Oceania	162734	Green
Jan'24	Oceania	162734	Green
Jan'24	Oceania	162734	Blue
Nov'23	Oceania	185450	Green
Nov'23	Oceania	185450	Blue
Nov'23	Oceania	185450	Green
Nov'23	Oceania	185450	Blue
Nov'23	Oceania	185450	Orange
Oct'23	Oceania	185450	Blue
Oct'23	Oceania	185450	Green
Oct'23	Oceania	185450	Blue
Oct'23	Oceania	185450	Blue
Oct'23	Oceania	185450	Orange

Thanks,

PBI V2

[Greg_Deckler · Accepted solution]

@Anonymous Maybe something like this although I am still not clear on when something goes "green" in your matrix:

Measure 2 = 
    VAR __Latest = MAX('Table'[Custom])
    VAR __Table = FILTER( ALL('Table'), [Custom] <= __Latest && [Status_Color] = "Green" )
    VAR __Regions = DISTINCT( SELECTCOLUMNS( __Table, "__Region", [Region] ) )
    VAR __Result = SUMX( FILTER( 'Table', [Region] IN __Regions ), [Sales Amount] )
RETURN
    __Result