Have a question regarding using both power pivot and pivot table together

Question

Have a question regarding using both power pivot and pivot table together

Summer Lane 21

Please see attached screenshot:
I have 1 regular table, power pivot data model, power pivot table
I suspect I wrote the wrong measures for what I wanted to achieve.

This is my measure: Current total$:=SUMX(DISTINCT(Table1[CURRENT]),SUM([Sales$]))
The pivot table shows the current total $ for Sarah is 400, however I thought my measure has put a filter on "current", that SARAH is not on the current list, so for Sarah, the sales should be 0.

How do I modify the measure or add one to get 0 for Sarah's current total?

Emi Zhang-MSFT 30,046 Reputation points Microsoft External Staff

2022-12-30T07:55:28.477+00:00

You should create 2 tables for different Act, and then add 2 tables into the model and create the connection.
Summer Lane 21 Reputation points

2022-12-30T14:41:02.553+00:00

Thank you very much! Is there another way of using measure to get the same results??

Accepted answer

0 additional answers

Your answer

Emi Zhang-MSFT 30,046 Reputation points Microsoft External Staff

2022-12-30T07:55:28.477+00:00

You should create 2 tables for different Act, and then add 2 tables into the model and create the connection.
Summer Lane 21 Reputation points

2022-12-30T14:41:02.553+00:00

Thank you very much! Is there another way of using measure to get the same results??

Answer 1

Herbert Seidenberg 1,191

Excel 365 Pro Plus with Power Pivot and Power Query.
With PQ Append().
https://www.mediafire.com/file/w3wcewu3lw1w7h5/01_01_23.xlsx/file
https://www.mediafire.com/file/texgrssre7vgnha/01_01_23.pdf/file

Share via

Have a question regarding using both power pivot and pivot table together

0 additional answers

Your answer