Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Power BI - Overall calculation for a matrix

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

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      Hello Greg_Deckler ,

       

      Thanks for your response.

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

      MonthRegionSales AmountStatus_Color
      Dec'23America277833Blue
      Dec'23America277833Blue
      Dec'23America277833Blue
      Jan'24America216333Green
      Jan'24America216333Green
      Jan'24America216333Blue
      Nov'23America277833Green
      Nov'23America277833Blue
      Nov'23America277833Green
      Nov'23America277833Blue
      Nov'23America277833Orange
      Oct'23America277833Blue
      Oct'23America277833Green
      Oct'23America277833Blue
      Oct'23America277833Blue
      Oct'23America277833Orange
      Dec'23Asia Pacific100572Blue
      Dec'23Asia Pacific100572Blue
      Dec'23Asia Pacific100572Blue
      Jan'24Asia Pacific91700Green
      Jan'24Asia Pacific91700Green
      Jan'24Asia Pacific91700Blue
      Nov'23Asia Pacific100572Green
      Nov'23Asia Pacific100572Blue
      Nov'23Asia Pacific100572Green
      Nov'23Asia Pacific100572Blue
      Nov'23Asia Pacific100572Orange
      Oct'23Asia Pacific100572Blue
      Oct'23Asia Pacific100572Green
      Oct'23Asia Pacific100572Blue
      Oct'23Asia Pacific100572Blue
      Oct'23Asia Pacific100572Orange
      Dec'23EMEA123564Blue
      Dec'23EMEA123564Blue
      Dec'23EMEA123564Blue
      Jan'24EMEA116380Green
      Jan'24EMEA116380Green
      Jan'24EMEA116380Blue
      Nov'23EMEA123564Green
      Nov'23EMEA123564Blue
      Nov'23EMEA123564Green
      Nov'23EMEA123564Blue
      Nov'23EMEA123564Orange
      Oct'23EMEA123564Blue
      Oct'23EMEA123564Green
      Oct'23EMEA123564Blue
      Oct'23EMEA123564Blue
      Oct'23EMEA123564Orange
      Dec'23Europe212378Blue
      Dec'23Europe212378Blue
      Dec'23Europe212378Blue
      Jan'24Europe197720Green
      Jan'24Europe197720Green
      Jan'24Europe197720Blue
      Nov'23Europe212378Green
      Nov'23Europe212378Blue
      Nov'23Europe212378Green
      Nov'23Europe212378Blue
      Nov'23Europe212378Orange
      Oct'23Europe212378Blue
      Oct'23Europe212378Green
      Oct'23Europe212378Blue
      Oct'23Europe212378Blue
      Oct'23Europe212378Orange
      Dec'23Oceania185450Blue
      Dec'23Oceania185450Blue
      Dec'23Oceania185450Blue
      Jan'24Oceania162734Green
      Jan'24Oceania162734Green
      Jan'24Oceania162734Blue
      Nov'23Oceania185450Green
      Nov'23Oceania185450Blue
      Nov'23Oceania185450Green
      Nov'23Oceania185450Blue
      Nov'23Oceania185450Orange
      Oct'23Oceania185450Blue
      Oct'23Oceania185450Green
      Oct'23Oceania185450Blue
      Oct'23Oceania185450Blue
      Oct'23Oceania185450Orange


      Thanks,

      PBI V2

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        2 years ago

        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