cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
powerbiwork
Helper I
Helper I

Total for results of a measure

Hi, 

 

So I have to create a measure for a couple of conditions. If it is true, then use the following formula, if it is false then -1. 

powerbiwork_0-1636650925330.png

 Which has worked. But then I want the total of the results which did not work. How do get the correct sum of the results of the measure? 

powerbiwork_1-1636651034809.png

 

 

 

Thank you

1 ACCEPTED SOLUTION
v-kalyj-msft
Community Support
Community Support

Hi @powerbiwork ,

According to your description, here’s my solution.

1.Creteat a measure like you.

Measure1 =

IF (

    SUM ( 'Opportunity'[Total ACV(USD)] ) = 0

        && SUM ( 'Opportunity'[One Off License(USD)] ) > 0,

    SUM ( 'Opportunity'[Positive_License_cost_c(USD)] )

        * SUM ( 'Commission-Model'[Commission:Rates0-50%] )

        * SUM ( 'Commission-Model'[ACV-First-Yr-Maint] ),

    IF (

        SUM ( 'Opportunity'[Total ACV(USD)] ) > 0

            && SUM ( 'Opportunity'[One Off License(USD)] ) > 0,

        -1

    )

)

2.Create another measure for the result.

vkalyjmsft_0-1636964731729.png

 

Best Regards,
Community Support Team _ kalyj

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

3 REPLIES 3
v-kalyj-msft
Community Support
Community Support

Hi @powerbiwork ,

According to your description, here’s my solution.

1.Creteat a measure like you.

Measure1 =

IF (

    SUM ( 'Opportunity'[Total ACV(USD)] ) = 0

        && SUM ( 'Opportunity'[One Off License(USD)] ) > 0,

    SUM ( 'Opportunity'[Positive_License_cost_c(USD)] )

        * SUM ( 'Commission-Model'[Commission:Rates0-50%] )

        * SUM ( 'Commission-Model'[ACV-First-Yr-Maint] ),

    IF (

        SUM ( 'Opportunity'[Total ACV(USD)] ) > 0

            && SUM ( 'Opportunity'[One Off License(USD)] ) > 0,

        -1

    )

)

2.Create another measure for the result.

vkalyjmsft_0-1636964731729.png

 

Best Regards,
Community Support Team _ kalyj

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

Thank you!

jdbuchanan71
Super User
Super User

@powerbiwork 

You must consider, on the total row the model does not look at any of the rows above.  It is calculated completely separate and independent.  Take a look at this article that discusses the topic.

https://www.sqlbi.com/articles/obtaining-accurate-totals-in-dax/

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.