Measure behaves different in power bi then in playground

[AntonyKlipperAS]

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

[igorlukanin]

Hi @AntonyKlipperAS 👋

Thanks for a detailed question.

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.

I suppose that you're using the SQL API to connect Power BI, are you not? So, the issue here is that Power BI will treat all numeric columns that the SQL API exposes as sum measures. There's no way to hint Power BI otherwise when just SQL API is used. Of course, you can change the measure types yourself—that is a viable solution here.

An alternative solution would be to use Semantic Layer Sync in Cube Cloud. It would export Power BI report templates in a format that Power BI will correctly recognize. Also, I believe Cube Cloud will get DAX support at some point—that would be the recommended way to work with Power BI then.

[AntonyKlipperAS]

Hi @igorlukanin,

Yes that is correct, I'm using the SQL API. Am I correct in my understanding that the Semantic Layer Sync for Power BI will not support user-based authentication? Because that would be a problem as well.

I'm trying to change the measure in Power BI to average but that also is not working.

Kind regards,
Antony

[AntonyKlipperAS]

Hi @igorlukanin

I did some test with the Semantic Layer Sync but still did not get the expected result.

All my foreign keys, are still not recognised as attributes and power bi wants to do a summation on them.

And my metrics only work if I choose the option 'Don't Summarize'