DAX Rate based on total amount per country

[Anonymous]

Hi,

I have blow table with all the columns except 'Rate' and'Total Amount Per Country', the Rate depends on the 'Total Amount Per Country' using SWITCH, how could I get the column 'Total Amount Per Country'. I don't need to have the Country Column in the visual.

'Total Amount Per Country' for australian states is = the sum of Amount for ACT+NSW+QLD+SA+TAS+VIC+WA = $200,00

'Total Amount Per Country' for new zealand states is = the sum of Amount for AKL +CHC = $100,00

[Anonymous · Accepted solution]

it is working now, I just needed to add below to the filter :

'Table'[countrty] = EARLIER('Table'[Country])

Thank you so much for your help.

[sturlaws]

Hi @Anonymous,

I would add a country column in the table, and use this measure:

Pr country =
VAR _country =
    CALCULATE ( SELECTEDVALUE ( 'Table'[Country] ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] );
        FILTER ( ALL ( 'Table' ); 'Table'[Country] = _country )
    )

 
This will work even if the country is not visible in the table visual

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

Thank you so Sturlaws, it worked like a charm.

I have used the same logic to add another selected value, it worked as well (as below code), But the total for each state is blank, do you know why? and when I drill up to the state level, thre is no value for the measure, it is blank:

Pr country_Type =
VAR _country =
CALCULATE ( SELECTEDVALUE ( 'Table'[Country] ) )
VAR _type =
CALCULATE ( SELECTEDVALUE ( 'Table'[Type] ) )
RETURN
CALCULATE (
SUM ( 'Table'[Amount] );
FILTER ( ALL ( 'Table' ); 'Table'[Country] = _country && 'Table'[Type] =_type)
)

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

You may create a measure as below.

Result = 
var state = SELECTEDVALUE('Table'[State])
return
IF(
    ISFILTERED('Table'[Location]),
    SUM('Table'[Amount]),
    IF(
        ISFILTERED('Table'[State]),
        SUMX(
            FILTER(
                SUMMARIZE(
                    ALLSELECTED('Table'),
                    'Table'[Country],
                    'Table'[State],
                    "total",SUM('Table'[Amount])
                ),
                'Table'[State] = state
            ),
            [total]

            
        ),
        SUMX(
                SUMMARIZE(
                    ALLSELECTED('Table'),
                    'Table'[Country],
                    'Table'[State],
                    "total",SUM('Table'[Amount])
                ),
                [total]
        )
    )
)

You may use a matrix visual to display the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Allan,

May be i did not explained it well.

The valued under 'Pr Country_Type' is grouped by Country but not grouped by location, what I want is to group by Country and column 'Table'[Type], please see below screenshot.

For exmaple under the Dedired table, for ACT the value for 'Total Pr country_Type' for location A is the sum of 'Pr country_Type' for all the locations with Type =1. so any location has the Type = 1, the sum should be the same.

The same goes for NZ.

I hope it is more clear now.

[sturlaws]

@Anonymous,

could you provide some sample data? Or a sample report? It will make it easier to help you

Cheers,
Sturla

[Anonymous]

Hi Sturla,

How can I send data? i don't see any attachment feature.

I can't send the pbxi file because it is using the tabular model.

[v-alq-msft]

Hi, @Anonymous 

I modify the table as below.

Table:

You may create a measure as below.

Total Pr country Type = 
VAR t =
    ALLSELECTED ( 'Table' )
RETURN
    SUMX (
        SUMMARIZE ( 
            'Table', 
            'Table'[State], 
            'Table'[Location],
            'Table'[Type]
        ),
        CALCULATE (
                SUM ( 'Table'[Pr country_Type] ),
                FILTER ( t, 'Table'[Type] = EARLIER('Table'[Type]) )
        )
    )

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for that, it does not group by country.

I have add the Country to the summarize function, but it did not work.

[Anonymous · Accepted solution]

it is working now, I just needed to add below to the filter :

'Table'[countrty] = EARLIER('Table'[Country])

Thank you so much for your help.

[Anonymous]

Actually I've one last question.

I want to filter 'Table'[Amount] for certain dates, how can I can do that?

I have tried below but did not work:

Total Pr country Type = 
VAR t =
    ALLSELECTED ( 'Table' )
RETURN
    SUMX (
        SUMMARIZE ( 
            'Table', 
            'Table'[State], 
            'Table'[Location],
            'Table'[Type]
        ),
        CALCULATE (
                SUM ( 'Table'[Pr country_Type] ),
                FILTER ( t, 'Table'[Type] = EARLIER('Table'[Type]) ),
                FILTER ('DATE', 'DATE'[MonthYear] >= 201911
        )
    )

[sturlaws]

@Anonymous, do you have a relationship between 'Table' and 'DATE'?

[Anonymous]

Yes i do @sturlaws .

[Anonymous · Accepted solution]

Why is this not in English?