Solved: How to divide 2 values filtered by date and name

tvathis · ‎07-24-2023

Hi!

I am trying to divide the top table by the bottom table.

The top table values are from a column I created multiply two other columns.

I'm not quite sure how to get the last names to match up and also the dates to match up.

For example, I'm looking to get the value when I divide 1.90/48 in a table.

They are also from 2 different tables I loaded into Power BI.

Thank you in advance for any help you can provide.

Terri

v-yanjiang-msft · ‎07-25-2023

Hi @tvathis ,

According to your description, here's my solution.

Create a measure:

Measure =
VAR _A =
    SUM ( charges[RVU Amount] )
VAR _B =
    SWITCH (
        STARTOFMONTH ( 'charges'[begin_date_of_service] ),
        DATE ( 2022, 12, 1 ), SUM ( 'Last Name (Hours)'[12/1/2022] ),
        DATE ( 2023, 1, 1 ), SUM ( 'Last Name (Hours)'[1/1/2023] ),
        DATE ( 2023, 2, 1 ), SUM ( 'Last Name (Hours)'[2/1/2023] ),
        DATE ( 2023, 3, 1 ), SUM ( 'Last Name (Hours)'[3/1/2023] ),
        DATE ( 2023, 4, 1 ), SUM ( 'Last Name (Hours)'[4/1/2023] ),
        DATE ( 2023, 5, 1 ), SUM ( 'Last Name (Hours)'[5/1/2023] )
    )
RETURN
    DIVIDE ( _A, _B )

Put measure in the matrix values, get correct result:

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-yanjiang-msft · ‎07-25-2023

Hi @tvathis ,

According to your description, here's my solution.

Create a measure:

Measure =
VAR _A =
    SUM ( charges[RVU Amount] )
VAR _B =
    SWITCH (
        STARTOFMONTH ( 'charges'[begin_date_of_service] ),
        DATE ( 2022, 12, 1 ), SUM ( 'Last Name (Hours)'[12/1/2022] ),
        DATE ( 2023, 1, 1 ), SUM ( 'Last Name (Hours)'[1/1/2023] ),
        DATE ( 2023, 2, 1 ), SUM ( 'Last Name (Hours)'[2/1/2023] ),
        DATE ( 2023, 3, 1 ), SUM ( 'Last Name (Hours)'[3/1/2023] ),
        DATE ( 2023, 4, 1 ), SUM ( 'Last Name (Hours)'[4/1/2023] ),
        DATE ( 2023, 5, 1 ), SUM ( 'Last Name (Hours)'[5/1/2023] )
    )
RETURN
    DIVIDE ( _A, _B )

Put measure in the matrix values, get correct result:

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

tvathis · ‎07-26-2023

This is exactly what I wanted.

I can't thank you enough!

I really appreciate it!!

foodd · ‎07-24-2023

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

tvathis · ‎07-25-2023

Below are links to the files. The top table in the excel spreadsheet is what I am trying to acheive.

I am trying to calculate th RVUs per hour for each physician.

https://onsiteneo-my.sharepoint.com/:u:/g/personal/tvathis_onsiteneonatal_com/EaDRqxLib-VFs8icp6K4sj...

https://onsiteneo-my.sharepoint.com/:x:/g/personal/tvathis_onsiteneonatal_com/ESCYlVhiGuFLgCHbR1f7VP...

Hours.xlsx

Thank you!

foodd · ‎07-25-2023

The Onedrive sharing links are failing authentication. Are you able to set the sharing to Anonymous, or is there a policy in place against anonymous sharing? Do you have another sharing portal available for external partners that can share anonymously?