Data allocation within Power BI

[hidenseek9]

Hello Power BI community

I have a question on how to allocate % of data within Power BI.

Please find below as a sample data I created. 

Sample Data 

What I have is a dataset showing a monthly gross sales by store.

I am connecting store data with store segment information to show a summary data.

(Please refer to below)

From here, I would like Power BI to one more step, which is to allocate 10% (rounded) of Avocado Sales to Fruits segment

and leave 90% of sales to Vegetables segment. 

So the expected output is below. 

How can I create a measure to have an output like this in Power BI?

Your support is much appreciated. 

Many thanks,

H

[v-alq-msft]

Hi, @hidenseek9 

Based on my research, you may create a calculated table as follows.

ModifiedCategory = UNION(Category,DATATABLE("Store",STRING,"Segment",STRING,{{"Avocado","Fruits"}}))

Then you could create a measure as below.

Sales = 
SUMX (
        'ModifiedCategory',
        VAR s = [Store]
        VAR e = [Segment]
        RETURN
            SWITCH (
                TRUE (),
                s = "Avocado"
                    && e = "Vegetables", CALCULATE ( SUM ( 'Data'[Gross Sales] ), 'Data'[Store] = s ) * 0.9,
                s = "Avocado"
                    && e = "Fruits", CALCULATE (
                    SUM ( 'Data'[Gross Sales] ),'Data'[Store] = s
                ) * 0.1,
                CALCULATE ( SUM ( 'Data'[Gross Sales] ), 'Data'[Store] = s )
            )
    )

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.

[hidenseek9]

@v-alq-msft 

Thank you for your reply!

It works perfectly in the sample data.

The actual dataset that I have is more complex and I am not sure how to apply the measure you provided with my dataset.

In my dataset, I have more than just 1 sales that I would like to allocate.

Please refer to the sample data 2 below.

Sample Data2 

Basically, the current output before applying your solution is below.

I should have made my original data shared more complex, but your support is very much appreciated.

Many thanks,

H

The expected output is below.

[v-alq-msft · Accepted solution]

Hi, @hidenseek9 

The sample data 2 link is not available. You may try to modify the calculated table and the measure as follows.

calculated table:
ModifiedCategory = 
UNION(
    Category,
    DATATABLE("Store",STRING,"Segment",STRING,{{"Avocado","Fruits"}}),
    DATATABLE("Store",STRING,"Segment",STRING,{{"Orange","Vegetables"}}),
    DATATABLE("Store",STRING,"Segment",STRING,{{"Cucumber","Animals"}})
)

measure:
Sales = 
SUMX (
        'ModifiedCategory',
        VAR s = [Store]
        VAR e = [Segment]
        RETURN
            SWITCH (
                TRUE (),
                s = "Avocado"
                    && e = "Vegetables", CALCULATE ( SUM ( 'Data'[Gross Sales] ), 'Data'[Store] = s ) * 0.9,
                s = "Avocado"
                    && e = "Fruits", CALCULATE (
                    SUM ( 'Data'[Gross Sales] ),'Data'[Store] = s
                ) * 0.1,
                s = "Orange"
                    && e = "Vegetables", CALCULATE ( SUM ( 'Data'[Gross Sales] ), 'Data'[Store] = s ) * 0.25,
                s = "Orange"
                    && e = "Fruits", CALCULATE (
                    SUM ( 'Data'[Gross Sales] ),'Data'[Store] = s
                ) * 0.75,
                s = "Cucumber"
                    && e = "Vegetables", CALCULATE ( SUM ( 'Data'[Gross Sales] ), 'Data'[Store] = s ) * 0.95,
                s = "Cucumber"
                    && e = "Animals", CALCULATE (
                    SUM ( 'Data'[Gross Sales] ),'Data'[Store] = s
                ) * 0.05,
                CALCULATE ( SUM ( 'Data'[Gross Sales] ), 'Data'[Store] = s )
            )
    )

Best Regards

Allan

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

[hidenseek9]

@v-alq-msft 

Is there a way to have multiple allocation logic by year?

For instance, in year 2020, I would like to split "Avocado" sales 9:1 to vegetable and fruits.

But in 2021, I would like to split "Avocado" sales 8:2 to vegetables and fruits. 

I have a virtual date table such as below. 

Appreciate your support

H