cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
adumont Frequent Visitor
Frequent Visitor

Analyze in Excel of Azure Entreprise dataset - No measures

I've connected Power BI to our Azure Entreprise billing data using the AppSource "Microsoft Azure Enterprise, By Microsoft For Power BI (Analyze Azure consumption for your enterprise enrollment in Power BI)":

2016-09-08 11_49_26-Power BI.png

 

Today I wanted to try the Analyze in Excel feature, so I installed the Analyze in Excel updates, and clicked on the Analyze in Excel button.

 

I downloaded the .odc file and opened it in Excel, which created a workbook with a Pivot table connected to my dataset in PowerBI. So far, great!

 

Only problem is in Excel pivot, the data appear to have no measures. In the particular case of these dataset, which is to work on Azure consumption (costs,...), some numerical data like "Cost" should be seen as Measures, so people could work with those, and show Totals, Averages,... Otherwise I don't really see what I can do with the data in Excel.

 

When you try to drap the Cost field to Values:

 

2016-09-08 12_07_15-Book3 - Excel.png

It leads to:

 

2016-09-08 12_07_47-Microsoft Excel.png

 

 

I wasnt sure if it was a problem with the AppSource and how it "exposes" the data to Excel, or whether I had to do anything in Excel to turn the data into a measure, so I opened a power BI Sample dataset ("Retail Analysis Sample") also with Analyze in Excel, and this one correctly exposes some of the data as Measures, and as you can see in the screennshot below, I could drag "Total Stores" into the Values of my Pivot, which leads to the expected result:

 

2016-09-08 12_00_02-.png

 

So in my understanding, the AppSource "Azure Entreprise for PowerBI" isn't exposing the Cost field properly as a Measure to Excel. 

 

Is it something one can work around by himself from within Excel? (I haven't managed to do anything to turn Cost into a measure.

 

Otherwise, is there a way to get the AppSource devs to be aware of this issue and hopefully fix it?

 

Thanks in advance

8 REPLIES 8
Super User
Super User

Re: Analyze in Excel of Azure Entreprise dataset - No measures

You need to create a custom measures by writing the DAX code. If your original datasource have measures already defined, It will show in the Pivot table as measures. It doesn't automatically detect the numerical columns as measures. 

 

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
adumont Frequent Visitor
Frequent Visitor

Re: Analyze in Excel of Azure Entreprise dataset - No measures

Thanks for your reply. I would think the Cost field is already a Measure in the dataset, as it can be used as such in reports, as you can see:

2016-09-08 13_47_32-GN Nuevo - Power BI.png

 

 

2016-09-08 13_48_41-GN Nuevo - Power BI.png

 Where do you suggest I define the custom measure in DAX code? I'm using Power BI web interface. Regarding the Dataset, I didn't "design the model" (not sure how to say), it comes out of the box with the App Source "Azure Entreprise", which connects to my Azure Entreprise Agreement usage data.

adumont Frequent Visitor
Frequent Visitor

Re: Analyze in Excel of Azure Entreprise dataset - No measures

To add up to my previous reply, I really believe Cost (in this case) is already a Measure in the Dataset, as you can see from this screenshot (sigma symbol):

 

2016-09-08 14_05_49-Power BI.png

ascendbi Regular Visitor
Regular Visitor

Re: Analyze in Excel of Azure Entreprise dataset - No measures

Hi - to further clarify the response from @BhaveshPatel, the Sigma symbol you circled indicates that Power BI has "suggested" this can be a calculation as it is a numeric field.

 

In order for it to be usedin Analyze in Excel ODC, it needs to have a "Calculator" icon to the left of it. This would indcate that you created the Measure within the Model by Clicking New Measure and writing a DAX formula.

 

2016-09-08_15-47-38.png

Moderator v-qiuyu-msft
Moderator

Re: Analyze in Excel of Azure Entreprise dataset - No measures

Hi @adumont,

 

As mentioned in this article: Analyze in Excel

 

“Excel PivotTables do not support drag-and-drop aggregation of numeric fields. Your dataset in Power BI must have pre-defined measures”

 

It means we need to create a measure in Power BI dataset firstly, then when use the Analyze in Excel feature, we can drag this measure to PivotTable Value area. So in your scenario, as “cost” is a numeric field instead of a measure, the result you get is expected.

 

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

 

Best Regards,

Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Analyze in Excel of Azure Entreprise dataset - No measures

Thank You @v-qiuyu-msft for your clarification.

 

 

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Moderator v-qiuyu-msft
Moderator

Re: Analyze in Excel of Azure Entreprise dataset - No measures

Hi @adumont,

 

Are you satisfied with the our explanation? If that is a case, would you please mark helpful replies as answers, so that we can close the thread?

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
pixelicous Frequent Visitor
Frequent Visitor

Re: Analyze in Excel of Azure Entreprise dataset - No measures

Guys,

 

Can you care to explain how do i open up this dataset with powerbi desktop version? the content pack is only for web..

Tried using the .ODC file that i received when "Analyzing in excel" but that doesn't do..

 

A step by step would be greatly appreciated, i really want to use that analyze in excel tool, right  now i cant do that with this cost thing not showing as a measure!

thanks!