% of Total (Filtered)

[haider378]

AOA,

I have Matrix as attached



all these sales are filtered by product category and sales in million is a measure (sum of all sales)
I want to calculate % of sales by each customer and should be updated as product category change

Thanks

 

[Jeffrey Mahoney]

Look at using Subtotal
=SUBTOTAL(109,E2:E100) will sum only the unfiltered values in E2:E100

So for each row you can divide the sales by the total unfiltered sales at the bottom

 

[haider378]

Look at using Subtotal
=SUBTOTAL(109,E2:E100) will sum only the unfiltered values in E2:E100

So for each row you can divide the sales by the total unfiltered sales at the bottom

Your solution seems fit normal excel worksheet but what about powerbi

 

[Jeffrey Mahoney]

Well since Power Bi reads Excel and Subtotal is a native function, I believe it should work.

 

[citizenbh]

For Excel Power Pivot
-------------------------
1. Add table in Data Model
2. From PoverPivot Insert Pivot Table
3. From PivotTable Filds insert in Rows: Costumer
4. From PivotTable Filds insert in ∑Values: Sales 2x
5. Right click second column "Sales" and option "Show Values As/Rank Largest to Smallest.."
6. Right click column "Costumer" and option "More Sort Option/Descending (Z to A) by:" - pick "Second column 'Sales'"