Analyze in Excel

[pstanek]

I can open ods file in Excel. After I connect with power bi account, I can not calculate with all  values that I can use in power bi in power pivot table. I can put only measures into Sum value window in pivot table.

I mean only measures will appear in "sum movement". Other values will appear in "movement" and I can not put these values  into Sum value window in pivot table.

[JonathanA]

What you do if yuor dataset is only avaialble from a Content Pack and cannot be opened in PowerBI Desktop? Can you create a measure in the PowerBI Service?

[ankitpatira]

@pstanek yep that is right, you can't put all fields in Values in pivot table as you can do in power bi. You will need to create specific measures in power bi to be able to drop those into Values in pivot table.

[pstanek]

I can put no one only measures works.

When you say specific, you exactly what.

When I want to sum costs, I have to create measure sum(costs).

Or do you mean something else?

[v-qiuyu-msft]

Hi @pstanek,

I agree with @ankitpatira, what you get is expected. If you want to put those numeric fields in Power Pivot table ∑VALUES area, you need to create a measure to aggregate( sum, count, avg,...) those fields in Power BI firstly. For example, the numeric field is [SalesAmount], create a measure called TotalSalesAmount, TotalSalesAmount = Sum(Sales[SalesAmount]). Please refer to this article to create a measure: Tutorial: Create your own measures in Power BI Desktop.

When you open the .odc file which created from "Analyze in Excel", you will find the TotalSalesAmount measure under the ∑ pane in PivotTable fields. You can put it in the ∑VALUES area.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[pstanek]

Do you know If ti can work for dashboard. I have colleague with limited access to database. he can see only values and tables, that I share with through dashboard ( in filters, tables).

I do not want him to see all of our database, but only these things He is able to use it at work.

[avive]

Analyze in Excel for a report based on a shared dashboard is on its way, stay tuned...

[v-qiuyu-msft]

Hi @pstanek,

When we use Analyze in Excel feature, the .ODC file will contain the tables, fields, and measures from the Power BI dataset. So as you have limited the user permission on database side, users can see his own data on Power BI dataset, then in .ODC file, he also can only see his own data.

Best Regards,
Qiuyun Yu

[v-qiuyu-msft]

Hi @pstanek,

You can also configure RLS to limit user to access specified data. And RLS is also supported in ANalyze in Excel feature. See:

https://powerbi.microsoft.com/en-us/documentation/powerbi-service-whats-new/

Best Regards,
Qiuyun Yu