how to pick a denominator from a table

[Anonymous]

Hello 

I have a huge table which contains invoice number per batch such as  below

id    batch  invoice  lockbox  Nbinvoiceperbatch

1        a         a101      201         150

2        a         a106       201         150

...

100    a        a205        201           150

101    b        b25           201           60

102    b         b 28         201           60

...

and so on

I am able to make a key measure to calculate the number of distinct invoice per batch using the following dax

No of posted invoices=calculate(

distinctcount(Table1,table1[invoice]),filter (table1[batch] && table1[lockbox]))

This should give me the number of invoice in each lockbox that we find in a batch.

the I would like to get the denominator which is the number of invoice per batch ex; 150, 60

How is it possible to define a measure which give the number of invoice per batch (value available into the table)

Then I would like to make a ratio such as divide(number of distinct invoice, number of invoice per batch, 0) 

Does someone know how to do that

[amitchandak]

@Anonymous , is you issue resolved by the solution provided by @Greg_Deckler . If not provide what is the issue you are facing with some more details.

[Greg_Deckler · Accepted solution]

OK, here is what I did, three measures:

No of posted invoices = DISTINCTCOUNT('Table'[invoice])

Denominator = AVERAGEX('Table',[Nbinvoiceperbatch])

Ratio = DIVIDE([No of posted invoices],[Denominator],0)

PBIX file attached. No that is not a typo in the file name! 🙂 

[Greg_Deckler]

OK, I am going to take a look at this but that DAX does not appear to be in any way correct. DISTINCTCOUNT does not take 2 parameters. And FILTER takes a table argument first, not a column reference.

[amitchandak]

Not very clear.

Refer:https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390