Income Utilization DAX

[Ankurdatascienc]

In my excel I have two sheets where in sheet 1 (Spend sheet) there is a column with customer id , spend money and in sheet 2 (Income sheet) Customer id and income money. Income money is not repetitive but spend money is repetitive because one customer can buy multiple items. Also, Income money for all the customers are already given as average Income Now, In power bi I want to calculate their Income utilization % how to do it ?.

Note- The Income column has values in average only. Income is given for only 1 month but Spend is given for 6 months.

Please suggest me how I can calculate Income utlization % ? What DAX Formula or Measure I can create ?

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[hackcrr]

Hi, @Ankurdatascienc 

For Total Income (assuming the columns in the Income sheet are named Income and are already averaged), you can create a simple metric:

Total Income = SUM(Income[Income])

However, since you've given that this is the average and only for one month, you may need to multiply by the number of months (6) to get the total expected income (if that's a reasonable assumption):

Total Expected Income = SUM(Income[Income]) * 6

For total expenses (assuming the column name in the Spend sheet is Spend), you need to use the SUMX function in conjunction with the VALUES function to summarize expenses by customer ID because of duplicate customer IDs:

Total Spend = SUMX(VALUES(Spend[Customer ID]), SUM(Spend[Spend]))

Calculate the revenue utilization percentage:

Income Utilization % = DIVIDE(  
    [Total Spend],  
    [Total Expected Income],  
    0 
)

Please adjust the above steps and formulas to your specific data structure and needs. If the Income column in the Income sheet is already the total income for 6 months instead of the average, then you don't need to multiply by 6.

Best Regards,

hackcrr

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