cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Add a calculated value (from a measure) to some another measure in SUMX function

Hi,

 

We have a single table with a list of Virtual Machines and their day-wise consumption for a complete month. Each virtual machine, it has 31 separate entries for each day, respectively. We have calculated the value for cost as 

 

VM_Cost= Table1[Consumed Hours] * Table1[Cost_Per_Hour]

 

Now, along with this value, we also need to add a fixed component of say 10000. So 10000 has to be divided and applied equally to all the virtual machines.

 

Fixed_Cost = 1000/ DISTINCTCOUNT[VM Name]

 

But, when we add this to the above formula of VM_Cost, this value is getting added for each and every row that is for 31 days individually, and for every calculation, it is taking the distinct count value as 1.

 

How can we add this fixed value uniformly across all the running days for each virtual machine?

2 REPLIES 2
Highlighted
Frequent Visitor

Re: Add a calculated value (from a measure) to some another measure in SUMX function

Hi, cant you use measure ? Does not look like its something you would want to filter on?

Not sure how you are adding the fixed cost, share few of the dax snippets and a screenshot.

Highlighted
Frequent Visitor

Re: Add a calculated value (from a measure) to some another measure in SUMX function

VM_Cost = 

var instance_count = SUMX(VALUES(UsageDetails), CALCULATE(DISTINCTCOUNT(UsageDetails[Instance ID])))

return

{

    SUMX(VALUES(UsageDetails), 10 * RELATED('UsageDetails'[Consume Quantity])) + 

    (DIVIDE(DIVIDE([FixedPrice], instance_count ), DISTINCTCOUNT(UsageDetails[Day]))

}

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors