Dax formula to filter values and calculate correctly

[FredO]

Hello all!

I have been working some time with Power BI and more simple DAX formulas. Now I have a problem that I need support with:

I have three tables (Masterdata, Projectspent and Link Project to Country)

Masterdata:

Projectnumber        Description
P100                        New Tomatochopper
P200                        New Cucumberchopper
P300                        New Pineappleslicer

Projectspent
Projectnumber Month Spenttype Actuals
P100                 6          Directcost  3000

P100                 6           Other        4000

P200                 6          Directcost  4000

P200                 6          Other         3000
P300                 6          Directcost  5000

P300                 6          Other         2000

Link Project to Country

Country   Region Projectnumber Percentage
France     South    P100                 60%
Germany  North   P100                 40%
France      South    P200               100%
Germany  North     P300                50%
Spain        South     P300               20%
France       South    P300                30%

There is a 1:many relation from “Link Project to Country” to “Masterdata”
There is a 1:many relation from Masterdata to Projectspent

The purpose of my application is to see how much each project should be allocated to each country or Region (Percentage column determines how much should be allocated. Total allocation is always 100%.

How do I get Power BI to show data in correct way? I cannot get the DAX formula to work in a correct way.

Thanks!

FredO

[v-janeyg-msft · Accepted solution]

Hi, @FredO 

It’s my pleasure to answer for you.

According to your description,if you only want country and region in the visual ,you need to create a measure with 'summarize'.

Like this:

Allocation =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            LinkProjecttoCountry,
            [Country],
            [Region],
            [Projectnumber],
            [Percentage],
            "aaa",
                SUMX (
                    FILTER (
                        ALL ( Projectspent ),
                        [Projectnumber] = EARLIER ( LinkProjecttoCountry[Projectnumber] )
                    ),
                    [Actuals]
                )
        ),
        "allocation", [aaa] * [Percentage]
    ),
    [allocation]
)

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[DataInsights]

@FredO,

Try this measure:

Project Allocation = 
VAR vProject =
    MAX ( Masterdata[Projectnumber] )
VAR vActuals =
    SUM ( Projectspent[Actuals] )
VAR vPercent =
    MAX ( 'Link Project to Country'[Percentage] )
VAR vResult = vActuals * vPercent
RETURN
    vResult

Create table visual, using Projectnumber from Masterdata table.

[FredO]

Hello it worked fine, but when I remove projectnumber and description I get strange totals!

br

Fredrik