Pivot tables in Excel generated by Semantic model and dates

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.