Analyze in Excel of Azure Entreprise dataset - No measures

[adumont]

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)":

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:

It leads to:

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:

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

[pixelicous]

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!

[v-qiuyu-msft]

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

[BhaveshPatel]

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

[adumont]

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:

 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]

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):

[v-qiuyu-msft]

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

[BhaveshPatel]

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

Thanks & Regards,

Bhavesh

[ascendbi]

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.