Total two rows for a measure

[mohsenask]

@v-angzheng-msft 

All_Fees

[fee_category]

All_Payouts

[charge_id]

[billing_amount]

[gross]

MS_GROSS = sum(All_Payouts[gross])

MS_BillingAmt = (sum((All_Fees[billing_amount])))

MS_%_Applied = [MS_BillingAmt]/[MS_GROSS]

I want to calculate the MS_%_Applied on the total for both fee_category i.e. 3.09%

Once thats calculated, i need a calculated colum that checks if the '% Applied' > 2.05 then its PREMIUM else its NOT PREMIUM

[Anonymous · Accepted solution]

Hi @mohsenask ,

You could use SUMX() function to get the sum of measure.

For example:

Measure 4 = SUMX(ALL('Table'),[Measure 3])

 

Best Regards,

Jay

[amitchandak]

@mohsenask , I think these should work

MS_GROSS = sum(All_Payouts[gross])
MS_BillingAmt = (sum((All_Fees[billing_amount])))
MS_%_Applied = [MS_BillingAmt]/[MS_GROSS]

Then you can have a measure

if([MS_%_Applied ] >.0205 , "Premium", "Not Premium")

In case you need that as a filter explore bucketing

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[mohsenask]

Hi @amitchandak - i have those measures but MS_%_Applied is actually calculating the individual % for the fee_category

I want to calculate the % Applied = (% Applied of BillingAmt)  + (% Applied of Gross)  i.e. 3.08+0.11= 3.19 into another column?? or where?

So the % applied = 3.19 (for both network_fee and stripe_fee)

Then the measure if([MS_%_Applied ] >.0205 , "Premium", "Not Premium") can be created.

[amitchandak]

@mohsenask , You can not have column now you can measure

Both should be measure

M1 =

[% Applied of BillingAmt] + [% Applied of Gross]

Or

AverageX(values(Table[Charge id]) ,[% Applied of BillingAmt] + [% Applied of Gross]  )

[mohsenask]

@amitchandak 

M1 =

[% Applied of BillingAmt] + [% Applied of Gross]

are these two new measures to create and then sum them into M1 ?

[mohsenask]

@amitchandak 

To make myself clearer, these are two records for ONE charge ID. So one unique ID has two records. I want to calculate the total of the MS_%Applied for the two records for each charge_ID. There could be more rows for each charge ID too

[mohsenask]

@amitchandak @v-angzheng-msft 

Need to identify whether premium or not based on the total of MS_%_Applied for the two rows that belong to ONE charge_ID