Trying out consuming a pivot table by directly embedding a semantic model in Excel. I have two choices: Instert Table or Insert Pivot. (BTW, it's maddening that the column names in the Table option don't match that in the semantic model.) I choose to insert a Pivot Table. (BTW, it does utilize the column names as in the semantic model.) I drop a column in the value in container of the pivot dialog and it only presents Count and Distinct Count. I can insert a "normal" pivot table from the table produced from the Insert Table option consuming a semantic model and it will present me with the the full domain of options. Why is that? Any workarounds? I tried generating a calendar table and conntecting it to the fact table and then placing the date value from the Calendar table, but that didn't make any difference.

Hi jmg80525 Thanks lbendlin for your inputs, whatever suggested is correct. If you're seeing only limited aggregation options like "Count" or "Distinct Count" when working with a Power BI semantic model in Excel, the issue is likely due to how the fields are defined in the model. To address this, ensure that numeric fields you want to summarize are either explicitly defined as measures in Power BI or are used from fact tables where implicit aggregation is supported. For best results, open the dataset in Power BI Desktop and create measures for any fields you intend to summarize (e.g., Total Sales = SUM('Sales'[Amount]). Once published to the Power BI Service, these measures will appear in Excel PivotTables with full aggregation capabilities such as Sum, Average, etc. Also, when connecting to the dataset from Excel, choose “Insert PivotTable from Power BI dataset” instead of “Insert Table.” This approach ensures Excel fully recognizes the semantic model structure and provides the complete set of summarization options. If you're using a date field, make sure it comes from a properly configured Date table in Power BI with a valid relationship to your fact table. Thank you.

Pivot tables in Excel generated by Semantic model and dates

Forum Discussion

Related content

Lakehouse Table Generate Create Table

Unable to Generate Ontology from Semantic Model in Microsoft Fabric

Directlake semantic model, calculated table

Unable to delete auto-generated Semantic Model (empty, no lineage)

Semantic Model Refresh History For Particular Tables