How to calculate the Total sum of a measure

[Anonymous]

Hello all,

This will be my first question (wait for way more)

Having a Qlik background, I find it "difficult" to quickly adapt to the DAX logic (This is my first day working with PowerBI).

This is what I'm willing to do : 

translate someting like sum (total measure) in QLik into a DAX measure. 

Why?

I have :

- A dimension : COUNTRY

- Calculated Measure : sum(table(SALES))

What I want to do : calculate the % Sales per country 

To do that, in Qlik it would be sthing like :

sum(Sales)/ sum(total Sales) or sum(Sales) / sum(total {<Country>} Sales)

How to do this in PowerBI? Thanks

[Anonymous]

I think it would be : 

sum(table(Sales)) / Calculate(sum(table(Sales)),ALL(Table))

[Anonymous]

Hi @Anonymous ,

Adding the ALL function can be to return the total sum of sales. The below formula also work:

DIVIDE(sum(table(Sales)),Calculate(sum(table(Sales)),ALL(Table)))

If you want to group by country and calculate the percentage of sales in the country, you can refer to the following:

Percentage =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table' ), [COUNTRY] = MAX ( 'Table'[COUNTRY] ) )
    ),
    CALCULATE ( SUM ( 'Table'[Sales] ), ALL ( 'Table' ) )
)

Best Regards,

Stephen Tao

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

[Anonymous]

Hello,

Thank you for your time.

THis is how I've done, I've created 3 measures:

1) sum Units Sold = SUM(financials[Units Sold])

2) Total Units Sold = CALCULATE( SUM(financials[Units Sold])  ,ALL((financials[Country])))

3) % Units Sold = [sum Units Sold]/[Total Units Sold]

And it's good if I don't select a country (I have only one table so..)

The problem is when I select a country, the % becomes 100% since PBI only sees the data of the selected country.

Within Qlik, I explicitely say divide always by the total no matter the country I select :

Sum(Sales) / sum (total {<Country>} Sales)

How can I achieve this in PBI?

Thanks

[Anonymous · Accepted solution]

Hi @Anonymous ,

The ALL function ignores any filters(including slicers) that might have been applied.

In your case, you need to replace ALL function with ALLSELECTED function.

The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters(keep the filtering of slicers). 

Total Units Sold = CALCULATE( SUM(financials[Units Sold])  ,ALLSELECTED ((financials[Country])))

Best Regards,

Stephen Tao

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

[Greg_Deckler]

@Anonymous 
This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

[Anonymous]

I dont want to "compare", but there "must" be an easy way to do so?

I mean, what I'm aiming to do is way to simple to achieve in Qlik to be considered as an "aggregation problem" with DAX.

I'll try to better explain what I want to do : 

COUNTRY	| CALCULATED MEASURE	| TOTAL	| %
COUNTRY1	| 50	| 100 |	50% (50/100)
COUNTRY2	| 30	| 100 |	30% (30/100)
COUNTRY3	| 20	| 100 |	20% (20/100)