Analyze in Excel - how to get a Table Visual?

[viviank]

I'm new at "Analyze in Excel".  

The goal I have is to take a table visual, and replicate it in Excel. It needs to be updated as the data updates. In Power BI, the table visual looks like this:

SalesPerson    Office     Department      Total Sales

Doe, J.            Tokyo     Accounting       $120,000

Smith, B.        Toronto   Compliance      $101,000

Ng, F.             Chicago   Technology      $88,000

The Total Sales is the sum of all the Sales lines for a time period. This is a SUM() measure.

The Office and the Department are properties of the SalesPerson - i.e. Doe is in Tokyo in Accounting, Smith is in Toronto in Compliance.

Is there a way to use "Analyze in Excel" to get this table into Excel? Excel is requiring I use a Pivot Table, and then it seems I have to summarize by rows, which I don't want to do - it makes lots of extra rows with zeros - and it's not possible to see anymore which salesperson is in which department and which office.

Thank you!

[viviank · Accepted solution]

I've not found a way to do this - but that may be okay. I found a problem with my DAX measure which was causing all the extra zeros. In the measure, I was replacing blanks with 0. This was the root cause of all the zeros!

When I take this out, now I only see rows that have data in the Excel pivot table, which is what I want.

They're not *exactly* the way they are in the Power BI Table Visual, but I can work with this now that I'm only seeing non-blank and non-zero rows.

[v-easonf-msft]

Hi,   @viviank 

Thanks for your sharing.

If you consider this issue closed, please accept you last reply as solution.

Other community members will easily find the solution when they get the same issue.

Best Regards,
Community Support Team _ Eason