Visual Calculation - how to group sales on a table visual

[OceanExplorer]

Hi, in this table visual, both columns are created from visual calculation:

I want to add a new visual calcualtion column that sum sales by territory, desired output:

I wrote DAX quries such as below and researched PARTITIONBY function, but non works. Since the columns in this table visual are derived from visual calculations, I can't use Matrix. Is there a way to write a DAX for this new visual calculation column?

Sales by Territory = 

CALCULATE(

    SUM([Credit Amount]),

    [Row Label]

)

[Irwan]

hello @OceanExplorer 

please check if this accomodate your need.

create a new measure with following DAX.

Sales Amount by Territory = 
var _Territory = SELECTEDVALUE('Table'[Sales Territory])
Return
SUMX(
    FILTER(
        ALL('Table'),
        'Table'[Sales Territory]=_Territory
    ),
    'Table'[Sales Amount]
)

Hope this will help.

Thank you.

[OceanExplorer]

Thanks, Irwan! The measure doesn't work as the fields were not in the model but generated from the table visual, so we can't refer them in a measure. I created a sample report, the Sales Territory (new) and Sales Amount (new) were visual calculations too. 

 

[Irwan · Accepted solution]

hello @OceanExplorer 

try this in visual calculation.

Hope this will help.

Thank you.

[OceanExplorer]

Thanks a lot! This DAX works for our requirement. Just one more question, the column that used in PARTITIONBY() has to be the original field in the data model, correct? We can't partition by a visual calculation derived column, right? 

[Irwan]

hello @OceanExplorer 

Glad to be a help.

And yes it has to be original field as far as i know.

Thank you.