How keep the total amount of my initiative no matter number of categories to which it belongs

[Anonymous]

Hi,

I have a question, I'm working with data that refers to the initiatives, each initiative has its buget but each of these initiatives could belongs to a one or more categories, how I can divide these total amount per category respecting the total of each initiative?

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

Here is my test data.

Then add the Budget column in the Table 2.

Budget =
CALCULATE (
    SELECTEDVALUE ( 'Table'[Budget] ),
    FILTER ( 'Table', 'Table'[Initiative] = 'Table (2)'[Initiative] )
)

Now you could use DIVIDE() function to get the result.

Measure =
VAR a =
    CALCULATE (
        AVERAGE ( 'Table'[Budget] ),
        ALLEXCEPT ( 'Table', 'Table'[Initiative] )
    )
VAR b =
    CALCULATE (
        AVERAGE ( 'Table (2)'[Budget] ),
        ALLEXCEPT ( 'Table (2)', 'Table (2)'[Catagory] )
    )
RETURN
    DIVIDE ( b, a )

[JarroVGIT]

Do you have an example of your dataset and your desired results? I don't fully understand what you are trying to achieve.