cube
cube copied to clipboard
Measure behaves different in power bi then in playground
I created a measure to calculate an average of my sales.
This is my cube
cube(`bug_order`, {
sql: ` select 1 as "id", 100 as "amount", 1 as "fk_status"
UNION ALL
select 2 as "id", 200 as "amount", 1 as "fk_status"
UNION ALL
select 3 as "id", 300 as "amount", 1 as "fk_status"
UNION ALL
select 4 as "id", 500 as "amount", 2 as "fk_status"
UNION ALL
select 5 as "id", 600 as "amount", 3 as "fk_status"
`,
public: true,
joins: {
bug_order_status: {
sql: `${CUBE}."fk_status" = ${bug_order_status}."id"`,
relationship: `many_to_one`
},
},
dimensions: {
id: {
sql: `${CUBE}."id"`,
type: `number`,
primary_key: true
},
amount: {
sql: `${CUBE}."amount"`,
type: `number`
},
fk_status: {
sql: `${CUBE}."fk_status"`,
type: `string`,
format: 'id'
}
}
,
measures: {
countOrdersNew: {
sql: `${CUBE}."id"`,
type: `count`,
filters: [
{ sql: `${bug_order_status.status} = 'new'` }
]
},
avgSales: {
sql: `${CUBE}."amount"`,
type: `avg`
}
}});
but when I connect this cube to a view and connect that view into power bi, power bi does not recognize the measure as an average.
this is the code for my view:
view(`RecreateBug`, {
public: true,
cubes: [
{
join_path: bug_order,
includes: `*`
},
{
join_path: bug_order.bug_order_status,
includes: '*',
excludes: [
`id`
]
}
]
});
But when I import this view in power bi it want to sum it.
Am I doing something wrong?
Kind regards, Antony