DAX command to sum individual sites in a matrix?

[Anonymous]

Hello,

I have the following matix. I am trying to create a column which shows the total amount of a specific product produced at the Ship To location. In this case MEH and OX.

The SKU column shows the quantity that has been shipped from Cape to the respective site. In the "Site Produces" column I want to display specifically how much the respective Ship To location produces of the SKU. The way I have it showing in the image below is by using the Calculate function to filter by the site OX. 

The function is as follows: 

OX Produces = CALCULATE ( SUM ( 'table'[SKU_quantity] ), ALLEXCEPT('table','table'[prod_name]), 'table'[Ship To] = "OX")

This does what I need but only for the OX site. Is it possible to have this function change within the matrix for each specific Ship To location, for example, when I expand to MEH, I will see the "Site Produces" value for MEH?

 

Thanks in advance!

[Anonymous]

[Measure] =
CALCULATE (
SUM ( 'table'[SKU_quantity] ),
VALUES( 'table'[prod_name] ),
VALUES( 'table'[Ship To] )
ALL('table' )
)

[Greg_Deckler]

@Anonymous - So normally you would create a VAR __shipto = MAX('Table'[Ship to]) and you would use that in your filter.

If that does not help, 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]

@Greg_Deckler  Is this supposed to be a filter in the CALCULATE function?

I tried the follwing:

_Site Produces = CALCULATE (SUM('table'[SKU_quantity]),ALLEXCEPT('table','table'[prod_name]),[_Var_ShipTo]) 

This gives me the error "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed." 

[Greg_Deckler]

@Anonymous - I guess I was thinking:

OX Produces = CALCULATE ( SUM ( 'table'[SKU_quantity] ), ALLEXCEPT('table','table'[prod_name]), 'table'[Ship To] = Var_Ship_To)

I don't tend to use CALCULATE. 

[harshnathani]

Hi @Anonymous ,

See if this works.

Produces = CALCULATE ( SUM ( 'table'[SKU_quantity] ), ALLEXCEPT('table','table'[prod_name])

The filter context should work for each shipto site.

Regards,

Harsh Nathani