Forum Discussion

hidenseek9's avatar
hidenseek9
Post Patron
6 years ago
Solved

Data allocation within Power BI

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

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

4 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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's avatar
      hidenseek9
      Post Patron
      6 years ago

      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's avatar
        v-alq-msft
        Community Support
        6 years ago

        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.