cube icon indicating copy to clipboard operation
cube copied to clipboard

Measure behaves different in power bi then in playground

Open AntonyKlipperAS opened this issue 5 months ago • 3 comments

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.

image image

Am I doing something wrong?

Kind regards, Antony

AntonyKlipperAS avatar Sep 09 '24 09:09 AntonyKlipperAS