Measurement Calculations using Data from Two Different Tables & Model View Connection?

[cgpahnk]

Hi,

I'm struggling to connect my data from two different tables for my measurement.

Below I'll paste the measurement I created to try and calculate the rate "events" per unit delivered:

My data comes froms two separate sources, so two different tables, and I tried to make a Many:Many connection in the Model View so I identify the rate of events for every supplier.

Count of EventID divided by Delivery Quantity =

CALCULATE(

DIVIDE(

    COUNTA('Event Severity'[EventID]),

    SUM('SDP 2020-2023'[Delivery Quantity])),

FILTER(CROSSJOIN(ALL('SDP 2020-2023'[Supplier Name]),ALL('Event Severity'[Supplier Name])),

'SDP 2020-2023'[Supplier Name]='Event Severity Consolidated'[Supplier Name]))

This is what happens when I place the measurement on a visual (I hid my supplier names):

Can anyone help me with this?

[gadielsolis]

Please try the following DAX:

Events vs Amount =

    VAR _Events= COUNTROWS(Events)

    VAR _Amount=SUM(Amounts[Amount Delivered])

    VAR _Events_vs_Amount=DIVIDE(_Amount,_Events)

    RETURN

    _Events_vs_Amount

Worth to mentioning, I did not change the relationship therefore still using many to many relationship.

[cgpahnk]

I'm trying to get a percentage though, so basically

Events per Supplier/Amount per Supplier

[gadielsolis]

Sorry,

I'm not getting what you need, if you divide events per supplier by amount delivered this would give you a really low number

If you can put this in excel and show me the desired output I will be more than happy to help.

[cgpahnk]

Yes, my bad I should have clarified! The numbers should be low very low, and PowerBI will show these numbers with scientific notation, but with my calculation it showed all suppliers as having the same percentage.

[gadielsolis]

Ohhh, I understand.

You can try the following measure:

Events vs Amount =

    VAR _Events= COUNTROWS(Events)

    VAR _Amount=SUM(Amounts[Amount Delivered])

    VAR _Events_vs_Amount=DIVIDE(_Events,_Amount)

    RETURN

    _Events_vs_Amount

[cgpahnk]

Okay I was able to get that calculation to work, but whenever I go to filter by the supplier name it stops working. I think theres an issue with my model view connections.

I actually created a table with all of the Supplier Names with no duplicates and it still wants to create a Many:Many relationship

[cgpahnk]

For example, if I took the amount of events I had (15) and divided it the total amount delivered*100 I'd get a percentage, what I want to figure out is how to display this percentage for each unique supplier.

[gadielsolis]

Hello,

Not sure if I understand correctly,

My advice would be creating a separate table with the name of the suppliers and connect it to the fact tables to generate one to many relationships. 

If you could share a dummy file with some examples it would be great for a better understanding.

[cgpahnk]

Let me try creating a new table with the supplier names, but this is basically what my data looks like:

My "Amount Delivered" data is basically Units/Lbs, so what I'm trying to do is figure out how many Events happen per Unit/Lb (Amount Delivered)

EventID	Supplier Name
1	Supplier A
2	Supplier A
3	Supplier A
4	Supplier B
5	Supplier B
6	Supplier B
7	Supplier B
8	Supplier B
9	Supplier B
10	Supplier C
11	Supplier C
12	Supplier D
13	Supplier D
14	Supplier D
15	Supplier D

Amount Delivered	Supplier Name
1551431	Supplier A
143454	Supplier A
16786	Supplier A
34	Supplier B
65342	Supplier B
32677	Supplier C
57777	Supplier C
32111	Supplier C
567788	Supplier D
22223	Supplier D
4456	Supplier D