Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
I want to concatenate 2 table for 2 business units and create a dashboard using that info,but my issue is that they calculations are different for 1 measure.
e.g
Business Unit A
Sale = sum(sale)/Total (sale)
Business Unit B
Sale = avg(Sale)
I tried to sum( (sum(sale)/Total (sale)) + avg(sale)) --------it does not work
or avg( (sum(sale)/Total (sale)) + avg(sale)) -----also does not work
How can I go about doing that so that I can have 1 chart/pivot table for both BUs
Thanking you in advance
Can you provide sample data along with your expected result?
Business Unit A
Product | City | User | Sales |
Cheese | Alonso | Ben | 23 |
Butter | Vryburg | Jon | 12 |
Milk | Florida | Peter | 23 |
Bread | Florida | Brian | 66 |
Rice | Alonso | Ben | 23 |
Butter | Orlando | Jon | 12 |
Milk | Florida | Peter | 23 |
Bread | Florida | Brian | 66 |
Calculation = avg(Sales) --- lets assume you have a user as a dimesion
Business Unit B
Product | City | User | Amount | Trans_Type |
Rice | Alonso | Ben | 23 | Sale |
Samp | Vryburg | Jon | 12 | Order |
Milk | Florida | Peter | 23 | Order |
Bread | Florida | Brian | 66 | Return |
Rice | Alonso | Ben | 23 | Sale |
Samp | Orlando | Jon | 12 | Sale |
Milk | Florida | Peter | 23 | Order |
Bread | Florida | Brian | 66 | Order |
Calc = (sum({<Trans_Type={'Sale'}>}Sale)/sum(Sale)
now I want an average of sale for both BUs on 1 chart/pivot table
Sorry but still having one confusion, What is the difference of Amount for BU A and BU B?
You need to add "source" columns to the concatenated tables that will point what BU you use in the formula.
And expression might be:
if (Source = 'A')
sum(sale)/Total (sale)
else avg(Sale)
you need additional one attribute in the table with BU information (A,B). based on this field you can use it in the expression.