Forum Discussion

cgpahnk's avatar
cgpahnk
Frequent Visitor
2 years ago

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

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?

Need Help

9 Replies

  • gadielsolis's avatar
    gadielsolis
    Resolver III
    2 years ago

    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's avatar
      cgpahnk
      Frequent Visitor
      2 years ago

      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)

       

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

       

      Amount DeliveredSupplier Name
      1551431Supplier A
      143454Supplier A
      16786Supplier A
      34Supplier B
      65342Supplier B
      32677Supplier C
      57777Supplier C
      32111Supplier C
      567788Supplier D
      22223Supplier D
      4456Supplier D
  • gadielsolis's avatar
    gadielsolis
    Resolver III
    2 years ago

    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's avatar
      cgpahnk
      Frequent Visitor
      2 years ago

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

      Events per Supplier/Amount per Supplier

      • cgpahnk's avatar
        cgpahnk
        Frequent Visitor
        2 years ago

        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.