Forum Discussion
Create pivot table based on Power BI dataset
Anonymous
Did you use "Analyse in Excel" option as I've proposed?
If so - then Pivot should work.
You can only drag a Measure to the Values section - so create a measure, for instance:
In your FactTable there's a "Sales" column that contains the sales amount.
First make sure it is Decimal, then add a new Measure:
TotSales = SUM(FactTable[Sales])
That's it.
Now you can drag "TotSales" into the Values section.
It should work
Michael
You can accomplish this only if you publish your dataset to the PowerBI.com service. There, click on the options for dataset (...) and choose "Analyse in Excel" You will probably need to install some driver (it'll guide you through - it is simple) and then it'll download a .odc connection file which you can open in excel and see Pivot connected to the service. Good luck! Michael
I've just tried this and I'm unable to drag a field into the values section of the pivot table. The numbers in this field are stored as integers (I see the numbers symbol in the column heading) in the Power BI database, but if I drag the field into the rows section of the pivot table and test the values with the "ISTEXT" formula the result is "TRUE". Therefore I suspect the pivot table isn't allowing the field into the values section of the pivot table because the pivot table thinks the field is a text field? Any idea how to fix?
Thanks,
CM
Anonymous
Did you use "Analyse in Excel" option as I've proposed?
If so - then Pivot should work.
You can only drag a Measure to the Values section - so create a measure, for instance:
In your FactTable there's a "Sales" column that contains the sales amount.
First make sure it is Decimal, then add a new Measure:
TotSales = SUM(FactTable[Sales])
That's it.
Now you can drag "TotSales" into the Values section.
It should work
Michael