Measure Value

[Anonymous]

Hi All,

I have a situation where I'm trying to calculate a value based on a condition in another column in my dataset and need help here.

I have below 2 columns in my dataset.

Product_Renewal_ARR_USD
Product_Renewal_Total_ARR_USD

So I'm trying to calcuate Sum(Product_Renewal_Total_ARR_USD) up to 100%(<= MAX) of the Product_Renewable_ARR  groupby Opportunity ID. This means if Renewable ARR for an opportunity is say $1000 and Total ARR is $1500 at the moment so we should take $1000 as Total_ARR.

I tried the below measure but its not working.

_Product Renewed ARR = CALCULATE(SUM(VW_F_PRODUCT_RENEWALS[PRODUCT_RENEWABLE_TOTAL_ARR_USD]),VW_F_PRODUCT_RENEWALS[PRODUCT_RENEWABLE_TOTAL_ARR_USD] <= MAX(VW_F_PRODUCT_RENEWALS[PRODUCT_RENEWABLE_ARR_USD]),GROUPBY(VW_F_PRODUCT_RENEWALS,VW_F_PRODUCT_RENEWALS[OPPORTUNITY_ID]))

 Need help on how to proceed on this??

Regards

[Wilson_]

Hello Himanshu,

Can you please share a mock sample pbix file? (If you don't know how, please see the pinned thread in the forum.) I have an inkling on a solution but it'd be much simpler to help that way.

[Anonymous]

Hi Wilson,

PFB the link to the Pbix file.

https://drive.google.com/file/d/13VpRxc3smw84VYw_TwWSV70kVVqUH1VS/view?usp=sharing

Regards

[Anonymous]

Hi @Anonymous ,

I suggest you to try code as below to create a measure.

_Product Renewed ARR =
VAR _Group =
    SUMMARIZE (
        VW_F_PRODUCT_RENEWALS,
        [OPPORTUNITY_ID],
        [FISCAL_YEAR_QUARTER],
        "Group1", CALCULATE ( SUM ( VW_F_PRODUCT_RENEWALS[PRODUCT_RENEWABLE_TOTAL_ARR_USD] ) ),
        "Group2", CALCULATE ( SUM ( VW_F_PRODUCT_RENEWALS[PRODUCT_RENEWABLE_ARR_USD] ) )
    )
RETURN
    SUMX ( FILTER ( _Group, [Group1] <= [Group2] ), [Group1] )

Result is as below.

Best Regards,
Rico Zhou

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

[Anonymous]

Hi, Thanks for the update but I think the output is still not what is desired.
Ideally the value for the measure should be exatcly matching the value of the 1st column i.e Product Renewable ARR.

Let me try to re explain the issue. Here I want to show the value of the Renewed ARR upto 100% of the renewable value. So suppose an opportunity has $500 as Renewable ARR and $1500 as Renewed ARR( 2nd column in the visual) I want to create a measure which shows _Product_Renewed_ARR as $500 instead of $1500.

So If you'll look at the visual if Product Renewable ARR is $11,436K for FY25 Q1 and Renewed ARR is around 72K so I want a measure which shows Renewed ARR value upto only 100% ( <= Max of Renewable ARR). So the measure will show $11,436K as Renewed ARR instead of $72K.

Let me know if anything is still not clear.

FYI : Currently the measure suggested by you is only giving values where Renewed ARR <= Renewable ARR. In any data item where Total ARR value ( Renewed ARR ) is greater than Renewable ARR it is giving blank value.

Regards 

[Wilson_]

Himanshu,

If the value of the measure should be exactly matching the value of the first column, why do you need the measure? Can you not just use the first column?

In any case, I re-wrote your _Product Renewed ARR measure like this:

_Product Renewed ARR = 
VAR Opportunities = 
ADDCOLUMNS (
    VALUES ( VW_F_PRODUCT_RENEWALS[OPPORTUNITY_ID] ),
    "Max Renewable ARR", MIN ( [MAX_Renewable_ARR], SUM ( VW_F_PRODUCT_RENEWALS[PRODUCT_RENEWABLE_TOTAL_ARR_USD] ) )
)
VAR Result =
SUMX (
    Opportunities,
    [MAX_Renewable_ARR]
)

RETURN Result

The measure goes through the VW_F_PRODUCT_RENEWALS table, calculates the max renewable ARR for each opportunity, then sums it all up.

Please let me know if you needed something different.

P.S.: It might be tremendously helpful for you and anybody else looking at your code if it were formatted in a more human readable way. Check out SQLBI's DAX Formatter for a great resource to help with this. 😄

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[Anonymous]

I need a seperate measure because there are some other calculation as well where I need the original column.

Also the measure is working for most of the cases but even when there is no Total ARR ( Renewed ARR) value its still giving the Renewable ARR value which should not be the case.

It should give the value only when Total ARR value is there or > Renewable ARR

[Wilson_ · Accepted solution]

Himanshu,

Ah sorry, there was a typo in my measure. The SUMX should've been using the column name, not the measure. See below:

_Product Renewed ARR = 
VAR Opportunities = 
ADDCOLUMNS (
    VALUES ( VW_F_PRODUCT_RENEWALS[OPPORTUNITY_ID] ),
    "Max Renewable ARR", MIN ( [MAX_Renewable_ARR], SUM ( VW_F_PRODUCT_RENEWALS[PRODUCT_RENEWABLE_TOTAL_ARR_USD] ) )
)
VAR Result =
SUMX (
    Opportunities,
    [MAX Renewable ARR]
)

RETURN Result

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[Anonymous]

@Wilson_ Thanks for the help.
The measure is giving the desired result now.

Regards