Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Solved! Go to 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.
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.
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.
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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |