Power BI matrix, add a filtered percentage as column

[TomBelgium]

Hi all,

i have a table with invoice info per sector & year which I present in a matrix.
The rows are sector, columns are total per sector in amount and percentage. For percentage, I added a measure 

Procentueel = DIVIDE (

SUM ( PBIInvoice_Info[Total_Invoiced] ),

CALCULATE (

SUM (PBIInvoice_Info[Total_Invoiced] ),

ALL ( PBIInvoice_Info[SectorRFID] )

),

0

)

However, I also have a boolean value NewCustomer on which I also want to show a percentage. 

So per row, you get the sector, the total invoiced, percentage total invoiced sector vs total and a percentage of total invoiced sector which is new customer vs total. 

I don't see how to calculate that last one. 

Any tips, ideas?

[Anonymous · Accepted solution]

Hi @TomBelgium,

Current power bi does not support creating dynamic calculated column/table based on filter selections. They are working on different levels and you can't use the child to affect its parent levels.

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[TomBelgium]

Anyone any ideas?

[amitchandak]

@TomBelgium ,

Procentueel = DIVIDE (
CALCULATE (SUM ( PBIInvoice_Info[Total_Invoiced] ), filter(PBIInvoice_Info, PBIInvoice_Info[NewCustomer])),
CALCULATE (
SUM (PBIInvoice_Info[Total_Invoiced] ),
ALL ( PBIInvoice_Info[SectorRFID] )
),
0
)

or

Procentueel = DIVIDE (
CALCULATE (SUM ( PBIInvoice_Info[Total_Invoiced] ), filter(PBIInvoice_Info, PBIInvoice_Info[NewCustomer])),
CALCULATE (
SUM (PBIInvoice_Info[Total_Invoiced] ),
filter(ALL ( PBIInvoice_Info[SectorRFID] ), PBIInvoice_Info[NewCustomer])
),
0
)

[TomBelgium]

Thank you very much. In the matrix, I still don't get the result I am expecting.

I have following data

- Project A, 40 K, sector A, status new

- Project B, 40 K sector A, status new

- Project C, 151 K sector A, status old

All projects over all sectors are 3,888 K.

The percentage of the sector is fine at 6.04%.

However, the percentage  of new projects should be 37% (80 K of 231K) but I can't get that result. I used the three options I am having there (represent as total of column, total of row or total of end result. So I guess I am still doing something wrong.

The result I