DAX to divide number grouped by Area against the total number

[DebbieE]

I have a measure

Fact1 = SUM(Fact)

And when I create a card with this measure and a slicer of year / date and slice to March 2019 I get the total number in March. 

March 2019        103311

But Ive been asked to create another measure

So I need the Sum(Fact) grouped against all the Areas in a dim table

March 2019 East                 54534

March 2019 West               35355

March 2019 South             13422

then I need to divide the total Sum by Area Sum and then Multiply by 100

Month Year Area                 AreaFact     Total              AreaWithLogic

March 2019 East                 54534          103311          100

March 2019 West               35355          103311           700

March 2019 South             13422          103311            200

So if you are jut looking at the data without the Area slice and you are just seeing March 2019 the card would show 1000

I have a feeling I need to create a measure and use a calculate to add in a filter but I really dont know where to start with this one.

Any help would be really appreciated

[v-xicai · Accepted solution]

Hi @DebbieE ,

You can try to create measures AreaFact ,Total and AreaWithLogic like DAX below.

AreaFact =CALCULATE(SUM('Data'[Fact]),FILTER(ALLSELECTED(Data),Data[Area]=MAX(Data[Area])))

Total = CALCULATE(SUM('Data'[Fact]),ALL('Data'[Area]))

AreaWithLogic =DIVIDE(AreaFact ,Total )

Best Regards,

Amy

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

[DebbieE]

Thank you so much, That has worked

CALCULATE - Evaluates an expression in a context that is modified by the specified filters.

FILTER  - Returns a table that represents a subset of another table or expression.

ALLSELECTED - Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filter 

MAX - Returns the largest value in a column, or the larger value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order.

[Greg_Deckler]

Can you share sample source data? 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