Forum Discussion
Using Ratios between two tables for output depending on Filter
Here are the Steps
1) Create the relationship between two table using Customer_Type
2) Add the Following Calculated Column in Table 1 to get the %ages on which Targets will be determined
%_age =
DIVIDE (
Table1[Customer_Numbers],
CALCULATE (
SUM ( Table1[Customer_Numbers] ),
ALLEXCEPT ( Table1, Table1[Customer_Type] )
)
)3) Add the Calculated Columns for Targets
Target 1 = RELATED ( Table2[Year_1] ) * Table1[%_age]
and so on for Targets 2 and 3
Thank you very much for the feedback and information. The calculation works if you use all e.g. Segments and Shops, however if you would like to see the impact if you take away one Shop or Segment the number remain and do not update for the once selected only. SO what I would like to achieve is to take the Year_1 Numbers and they need to total to the selection made, also if only one or two Shops or segments are selected.
Yes that works 100% agree. So as an example if you select Customer_Type_1 and Shop_1&2 I would like to get the 100|200|400 result however now only split between Shop_1 and Shop_1:
Current Results:
Desired Output:
Power BI Output Shop Customer_Type Target_1 Target_2 Target_3 shop_1 Customer_Type_1 55.56 111.11 222.22 shop_2 Customer_Type_1 44.44 88.89 177.78
