Help with formula percentage with filter between multiple tables

[Anonymous]

Hi!

I need help creating a DAX formula. The result I want to get from the formula is:
the percentage of purchase orders per supplier and employee compared to the total purchase orders per employee.

I'm fairly new to DAX formulas, so I could use some help.

I have the following data:
- Table person drafter (= employee name)
- Formula number of suppliers (the value is always 1 when these are expanded in the matrix)
- Formula number of total purchase orders

I have made the following formula:
Unique number of purchase orders % =
CALCULATE
(DISTINCTCOUNT(d_orderorder[Order order code]),
FILTER(d_person_drafter, d_person_drafter[Employee]))

This formula is not shown in the table when I drag it in there

Visualization with column names =
Employee names - Number of suppliers where ordered - Number of purchase orders (per supplier and employee will be shown here)

After expanding the matrix, all the names of the suppliers per employee are shown where the order has been placed, so the value becomes 1 per supplier name, and the number of purchase orders per supplier. Next to this I want to have the column what the percentage is then.

Employee A has created a total of 3900 purchase orders at 107 suppliers. Of those 3900 orders, 600 were ordered from supplier X. That's 15%. That is the result I want to get from the formula.

Thank you very much for all the help!

[Anonymous]

Hi,

Unfortunately, this is not yet the solution. Below is an example of my matrix. I keep getting 100% on the expanded matrix.
I can't put the suppliers in the columns because we are talking about multiple suppliers per person.

Unfortunately, this is not yet the solution. Below is an example of my matrix. I keep getting 100% on the expanded matrix.
I can't put the suppliers in the columns because we are talking about hundreds of suppliers per person.

Matrix rows: d_person_drafter[Employee] + d_orderorder[Supplier] + Measure total of order code

No matrix columns

Matrix values: measurement number of suppliers + Measure total of order code + [Percentage] (measure from you @DataInsights )

This matrix:

the results above should look like this

Thanks!

[DataInsights]

@Anonymous,

I added the measures [Count Supplier] and [Count Order Code] as shown below. You have one additional field in Rows.

Is this the correct result? If not, would you provide a link to your pbix so I can analyze further? You can use sample data to illustrate the issue.

[Anonymous]

Hi!

It is the correct outcome. I don't think it works for me because I can't make a direct relationship between the different tables. I've tried to put this in the fact table (f_orderorderrule) using calculated columns, but then I still get 100% everywhere.

Is there a detour for this? As you can see above, the relationships are quite complex and I can't make any further changes to them. 

When I want to create a relationship between the purchase order and person drafter, I get the message that this is an ambiguous relationship and therefore cannot be executed.

[DataInsights]

@Anonymous,

I recommended reviewing and improving the data model (star schema is best). It will make calculations easier. Bidirectional cross filtering should be used only when absolutely necessary (one-to-one relationships are the exception, since it's the only option). If you need bidirectional cross filtering, you can use the CROSSFILTER function inside CALCULATE.

[DataInsights]

@Anonymous,

Try this solution.

1. Data model:

2. Measure:

Percentage = 
VAR vNumerator =
    COUNT ( d_orderorder[Order Code] )
VAR vDenominator =
    CALCULATE (
        COUNT ( d_orderorder[Order Code] ),
        ALLEXCEPT ( d_orderorder, d_person_drafter[Employee] )
    )
VAR vResult =
    DIVIDE ( vNumerator, vDenominator )
RETURN
    vResult

3. Matrix:

Matrix rows: d_person_drafter[Employee]

Matrix columns: d_orderorder[Supplier]

Matrix values: [Percentage] (measure above)

Table d_orderorder: