skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Partners Overview
    • Solutions Partners
    • BI Specialized Partners
    • Power BI CSOs
    • Fabric Partner Community
    • Training
    • Getting started
      • Overview
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Power BI forums
    • Updates
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish forums
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 

    Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

    • Power BI forums
    • Galleries
    • Quick Measures Gallery
    • Re: Measure Totals, The Final Word

    Re: Measure Totals, The Final Word

    10-26-2020 05:12 AM

    Anonymous
    Not applicable
    93297 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Measure Totals, The Final Word

    ‎10-21-2018 06:09 AM

    With apologies to Theodor Geisel...

     

    Measure totals have you perturbed?
    Fear not!
    It's Measure Totals, The Final Word,

     

    These measures work with matrices,
    They work with tables,
    They work with rows and columns and labels.

     

    They work in the daytime,
    They work at night,
    They work to make sure the totals are right!

     

    Now that you've seen them,
    Now that you've heard,
    Shout it out loud, it's Measure Totals, The Final Word!

     

    At some point, we've all been frustrated by measure totals. If you want to understand why, read this post.

     

    The technique employed here is fairly simple and should work in all "standard" cases of where you just want the Total line to, well, display the total (sum) of a measure. For more complex scenarios, see my Matrix Measure Total Triple Threat Rock & Roll measure.

     

    Essentially, create a measure, any measure, that performs your desired calculation and returns the correct result at the row level. This becomes your "m_Single" measure. Now, create an "m_Total" measure that performs a SUMMARIZE of your data, exactly as how it is displayed in your table or matrix and use the "m_Single" measure within that SUMMARIZE statement to provide the values for the individually summarized rows. Finally, perform a SUMX across that summarized table. The measures presented in this PBIX file also do a HASONEVALUE check that isn't really necessary in most cases but perhaps lends a little confidence to the user that the SUMX is only employed in the Total line and might also add some performance improvements.

     

    In effect, you are recreating the displayed visualization in memory as a table and then doing a summation across that table for the total line, as you would intuitively expect a total line in a table or matrix to work.

     

    So, if we have a measure like:

     

    m_Single = SUM(Table1[Value])-50

    This measure will cause problems in total lines. So, if we are summarizing by [Name], we create this measure:

     

     

    m_Total 1 = 
    VAR __table = SUMMARIZE('Table1',[Name],"__value",[m_Single])
    RETURN
    IF(HASONEVALUE(Table1[Name]),[m_Single],SUMX(__table,[__value]))

    If we are summarizing by [Category1], we create this measure:

     

     

    m_Total 2 = 
    VAR __table = SUMMARIZE('Table1',[Category1],"__value",[m_Single])
    RETURN
    IF(HASONEVALUE(Table1[Category1]),[m_Single],SUMX(__table,[__value]))

    And so on...

     

     

    We use these "m_Total" measures in our visualizations. The "m_Single" measure is still used, but not directly in the visuals themselves.

     

    Is it annoying to have to create multiple measures and specifically tailor them to each individual visual? Yes, yes it is.

     

     

     

     

     

     

    eyJrIjoiODBmNmI4YjItZTMwYi00ZDU4LTg0MWItMzYyZWU3ODk4ZWI4IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Preview file
    19 KB
    Measure Totals The Final Word.pbix
    Labels:
    • Labels:
    • Featured
    • Mathematical
    • Totals
    Message 1 of 55
    152,471 Views
    93
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to shivakoriginja
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-22-2021 05:23 AM

    @shivakoriginja - So that looks like your m_Single measure. You would need an m_Total measure like the following:

     

    m_Total = 
    VAR __table = SUMMARIZE('Table',[Venue],"__value",[Utilization])
    RETURN
    IF(HASONEVALUE(Table[Venue]),[Utilization],SUMX(__table,[__value]))

     


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 35 of 55
    86,150 Views
    1
    Reply
    shivakoriginja
    shivakoriginja
    Frequent Visitor
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-22-2021 05:45 AM

    shivakoriginja_0-1616417042680.png
    hi @Greg_Deckler,
    |there is no change in the result. still I am not getting 100% there are no filters applied.

     

    Message 36 of 55
    85,818 Views
    1
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-09-2020 01:47 PM

    @Greg_Deckler You, sir, just saved my day...   THANKS!!!

    Message 31 of 55
    93,529 Views
    0
    Reply
    Anonymous
    Not applicable
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-23-2020 02:18 AM

    I have fact table with 8 dimensions and average grand total should work based on selected dimension. Please guide me How to correct the grand total based on selections

    User validating this data from AAS using excel plugin. We can’t tell what dimension is going to selected for analysis

    Measure 1 = SUM(Amount)

    Measure 2 = Total days in 3 Months (Need to ignore the missing months based on dimension). 

    Avg = Measure 1 / Measure 2

    Sample data

    Days

    Period

    Client

    Channel

    Region

    Agent

    Product

    Amount

     

    31

    202001

    A1

    C1

    R1

    A1

    P1

    100

     

    29

    202002

    A1

    C1

    R1

    A1

    P1

    200

     

    31

    202003

    A1

    C1

    R1

    A3

    P2

    300

     

    31

    202001

    A2

    C1

    R1

    A3

    P4

    150

     

    29

    202002

    A3

    C1

    R1

    A1

    P1

    250

     

    31

    202003

    A2

    C1

    R1

    A1

    P1

    350

     

    31

    202003

    A2

    C2

    R2

    A2

    P1

    450

     

     

     

     

     

     

     

     

     

     

    Report Based on Client

    Amount

    Days

    Avg

     

     

     

     

     

    C1

    1350

    91

    14.84

     

     

     

     

     

    C2

    450

    31

    14.52

     

     

     

     

     

     

    1800

    91

    19.78

    Dax Calculation (Days in 3 Months At grand total)

     

     

     

     

    29.35

    Expected Avg Total

     

     

    Report Based on Agent

    Amount

    Days

    Avg

     

     

     

     

     

    A1

    900

    91

    9.89

     

     

     

     

     

    A2

    450

    62

    7.26

     

     

     

     

     

    A3

    450

    31

    14.52

     

     

     

     

     

     

    1800

    91

    19.78

    Dax Calculation (Days in 3 Months At grand total)

     

     

     

     

    31.66

    Expected Avg Total

     

     

     

    Tried some possible options to fix in AAS. 

    • SUMX(VALUES('Accounting Period'[Effective Date]),[ <Measure> (4 Months)])  -- Tried to re-calculate at day level
    • SUMX(VALUES('Accounting Period'[Year Month Number]),[ <Measure> (4 Months)])  -- Tried to re-calculate at Month level
    • Var Days = [DaysIn4Months]

               Return

              SUMX(Table,(Table[Column] / Days) -- Tried calculate at each row level .

    • If we use AVERAGEX function it is giving wrong average due to it is considering the no of days Transaction received (per month 21 / 22 days only – as we are not receiving transactions on weekends). User want to consider full days in month.

    To correct the grand totals we need to use below syntax. But this syntax we can't achieve while using Pivot tables.

    • IF(HASONEVALUE([Slicer]), <Measure> , SUMX(VALUES[Slicer]), <Measure>) 
    Message 32 of 55
    93,351 Views
    0
    Reply
    Anonymous
    Not applicable
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-26-2020 05:12 AM

    Hi All,

     

    Any ideas on above issue. AAS should calculate average grand total based on selected slicer in excel pivot table. I am happy to write multiple if conditions if we have solution for this. 

     

    How to find what all slicers selected to apply if condition.

     

    SUMX(

    SUMMARIZE(<Fact Table>, [Fact Table][Column1], [Fact Table][Column2], [Fact Table][Column3], "Measure Name", <Measure>),

    <Measure Name>)

     

    Will this works for all dimensions. I hope with this we might hit with performance issue.

     

    Please suggest some taughts to find solution for this

    Message 33 of 55
    93,297 Views
    0
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-01-2020 07:21 AM

    Hi,
    I have a calculated field Goal which is not giving the sum correct.

    How do I make it working?


    Goal =
    /* convert(max(rev_tasks[Goal_Months]),INTEGER)*/
    var vgoal=max(rev_tasks[Goal_Months])
    Return
    SWITCH(TRUE(),
    vgoal=BLANK(), BLANK(),
    vgoal="1",(1+MAX(rev_tasks[Goal_Perc]))*SUM(C_AR[Revenue]),
    vgoal="2", CALCULATE(SUM(C_AR[Revenue]),DATESINPERIOD('dates'[Date],MAX('dates'[Date]),-2,MONTH))*(1+MAX(rev_tasks[Goal_Perc])),
    vgoal="3", CALCULATE(SUM(C_AR[Revenue]),DATESINPERIOD('dates'[Date],MAX('dates'[Date]),-3,MONTH))*(1+MAX(rev_tasks[Goal_Perc])),
    vgoal="AR",[AR]
     
    )

     

    Message 30 of 55
    93,697 Views
    0
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-11-2020 03:52 PM

    Its 2020 - this had stumped me for 3 hours and yet here we are. Thank you sir, thank you so very much!

    Message 27 of 55
    93,987 Views
    2
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-11-2020 04:53 PM

    @Anonymous Don't feel bad man, this one comes up a LOT!! It is super unintuitive to people just learning DAX but is a great example to help expand your understanding of how important context is to DAX!


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 28 of 55
    91,079 Views
    0
    Reply
    rrjr007
    rrjr007
    Frequent Visitor
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-24-2020 08:35 AM

    Below is a screen shot from my excel file that I have uploaded into Power BI.

    I want to show that in Cook County (for my current period and for the fuel type water) that Brand 1 has sold a total of 8, which is equal to 13% share

    rrjr007_0-1600961488426.png

     

    In Power BI if I filter on Cook I get what I need.

     

    rrjr007_1-1600961488501.png

     

    When I add another County, the numbers get messed up.

     
     
     

    Where am I going wrong.  I’ve attached the pbix file.  I am not a Dax guy, I am trying to learn.  Thanks.

     

     

    test.pbix
    Message 29 of 55
    85,094 Views
    0
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-19-2020 11:47 PM

    Hi,

     

    It really solved my problem for one measure , but in another case am using the same approach that is giving the incorrect subtotal what can i do in that case i am not able to understand, I am using below measure after referring your another trick for subtotal-

     

    Volume Effect Row =
    Var NET_SALES_AMOUNT= SUM(INV_INVOICE_LINE[NET_SALES_AMOUNT])*0.93
    Var PY_Sales= [PY Sales]*0.94
    Return
    NET_SALES_AMOUNT-PY_Sales-[Price Effect amount]
     
    Volume Effect =
    VAR __tmpTable = SUMMARIZE(INV_INVOICE_LINE,'INV_INVOICE_LINE'[SBU_FAMILY],"Aggregation",
    [Volume Effect Row])

    VAR __SubTotal = SUMX(__tmpTable,[Aggregation])

    VAR __GrandTotal = SUMX(GROUPBY(__tmpTable,[SBU_FAMILY],"GTAggregation",SUMX(CURRENTGROUP(),
    [Aggregation])),[GTAggregation])

    RETURN
    IF(HASONEFILTER('INV_INVOICE_LINE'[SBU_FAMILY]) && HASONEFILTER('INV_INVOICE_LINE'[ITEM_NUMBER]),
    [Volume Effect Row],IF(HASONEFILTER('INV_INVOICE_LINE'[SBU_FAMILY]),__SubTotal,__GrandTotal))
     
    But it is not giving the subtotal of values
    Message 26 of 55
    63,424 Views
    0
    Reply
    joel888
    joel888
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-18-2020 01:00 PM

    hi - I used your solution at the lowest level of my hiearchy but now how do I get the numbers for aggregate up to the higher levels properly (see table below)?

    thanks.

     

    joel888_1-1595102297118.png

     

    Message 24 of 55
    59,294 Views
    0
    Reply
    rsbin
    Super User rsbin
    Super User
    In response to joel888
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-19-2020 06:07 AM

    @joel888 

    I suggest you post your question as a new thread in the "Help" Forum.   I don't think this thread should be used to ask questions for Help.  You will get many more eyes looking at it which will assist you further.

    Also please include as much detail as you can. 

    Good Luck and Best Regards,

    Message 25 of 55
    56,910 Views
    0
    Reply
    rsbin
    Super User rsbin
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-06-2020 03:58 PM

    @Greg_Deckler 

     

    Just wanted to say a big Thank You for this post.

    It helped me to get my Matrix Visual working the way it should!

     

    Kindest Regards,

    Message 23 of 55
    50,300 Views
    0
    Reply
    hullterry
    hullterry
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-17-2020 12:11 AM

    Firstly thanks Greg for this great article.

    One Question

    when dealing with a measure that is in itself a sumx , is there something i need to do differently in creating the variable table? 
    the formula works for each month but total is returnign zero and i ssupect it is due to the sumx formula not creating the summarizes table correctly.

     

     

    Message 21 of 55
    50,497 Views
    0
    Reply
    hullterry
    hullterry
    Frequent Visitor
    In response to hullterry
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-17-2020 06:48 PM

    After looking at this a bit have developed an alterative solution:

    i created an index column in the data table for the level of calculations i need ie Customer + Product+ Month

    Then i created a new table summarising that column and linked the new table back to the data table.

    Then in the sumx formula used the new table as the reference Sumx( new table (custProdMonthnum), calculations)

    this means it forces the total column to do the calcs at the month level, so all adds up. also it means just one formula and doesnt worry if you add in qrtly totals etc..

    Message 22 of 55
    34,362 Views
    0
    Reply
    Sparta100
    Sparta100 Advocate I
    Advocate I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎02-16-2020 07:45 PM

    Thank you Greg. I was able to obtain the correct totals for a simple (yet critical) sum of absolute values calculation in Power Pivot by adapting your technique. What I thought would be a three second "wrap in ABS" formula turned into a three (12-hour) day nightmare. Cannot believe what I just went through to achieve what is literally the dead-simplest calculation mathematically possible. But can't thank you enough for this solution.  

    Message 20 of 55
    27,975 Views
    0
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-09-2020 08:22 AM

    Do the formula work between two tables?

    Thanks for your help

    Message 19 of 55
    28,118 Views
    0
    Reply
    calerof
    calerof Impactful Individual
    Impactful Individual
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-22-2019 05:53 AM

    Hi @Greg_Deckler ,

    I have a question regarding the use of a VAR in the summarization. In that case the result is wrong. I have to use a VAR due to a complex commission calculation based on different levels of revenue and % margin. 

    For instance, if revenue > $250,000 and margin % is less than 56% but greater than 52% the commision % is 1%,

    with the same revenue limit if margin is less than 60% but greater than 56% the commission % is 1.5%, etc.

    To handle this calculation I created VAR as follows:

    Comision = 
    VAR Sales= [Sales Total]
    VAR Margin = [Margin Total]
    VAR Commission = 
        SWITCH( TRUE(),
            Sales >= 200000 && Sales < 250000, 0,
            Sales>= 250000 && Margin >= 0.52 && Margin < 0.56, 0.01,
            Sales>= 250000 && Margin < 0.60, 0.015,
            Sales>= 250000 && Margin >= 0.6, 0.02, 
        0
        )
    VAR BaseBonus = 
        SWITCH( TRUE(),
            Sales>= 200000 && Sales < 250000, 1000,
            0
        )
    VAR CommissionAmount = Sales * Commission + BaseBonus
    VAR ComisTotal = SUMMARIZE( VALUES( OCRD[SlpCode] ), OCRD[SlpCode], "ComisTot",  MAX( 0, CommissionAmount ) )
    RETURN
    SUMX( ComisTotal, [ComisTot] )

     

    At using a VAR after RETURN it gives a wrong total.

    I made a dummy data model to show this procedure here.

    Could you please advice?

     

    Thanks,

     

    Fernando

     

    Message 18 of 55
    23,150 Views
    0
    Reply
    OvidiuNeacsu
    OvidiuNeacsu Resolver I
    Resolver I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-02-2019 11:47 PM

    Hello everyone,

     

    I am trying to calculate some average times and it works well, except the Totals. Each row represents a candidate. 

     

    The table I have looks like this: 

    PBIDesktop_HitMfMLmGk.png

     

    The formula I use for the column in the middle is: 

     

    From Interview Scheduled to Offer = IF ( ISBLANK([_Average days to Offer]) || ISBLANK([_Average days to Interview - Scheduled]), BLANK(),([_Average days to Offer] - [_Average days to Interview - Scheduled]))
     
    The purpose of it is to ignore the calculation if the other column is blank and if that applies to give a blank result. Which works perfectly, except for the TOTALS. Is there something I can add to my formula so that the total is blank also unless the same candidate has both values? 
     
    The 1st and 3rd column are also calculations that apply to columns that contain whole numbers (number of days from application to Interview or Offer in this case) or null values, that apply to each candidate (as I said one row = 1 candidate) 
     
    _Average days to Interview - Scheduled = CALCULATE( AVERAGE([_Days Application to Interview - Scheduled]), FILTER(report, report[_Days Application to Interview - Scheduled] <> 0 ))
     
    _Average days to Offer = CALCULATE( AVERAGE([_Days Application to Offer]), FILTER(report, report[_Days Application to Offer] <> 0 ))
     
    I might be missing something here, can you please help me implement the "HASONEVALUE" function? Maybe it would be a solution here. 
     
    Thank you very much!
    Message 17 of 55
    15,794 Views
    0
    Reply
    maulopez
    maulopez Advocate I
    Advocate I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-22-2019 10:16 PM

    Amazing! I just tried this Greg's solution after days of struggling with meassure totals for the first time and it worked perfectly. It is indeed amaizing that Microsoft hasn't come out with a solution of their won for this problem alredy coded in PBI, but this solution is a real wonder. Thanks so much Greg, you're a genious ! BTW, the poem is fantastic too! : )

    Message 16 of 55
    13,862 Views
    0
    Reply
    samiller71
    samiller71
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-25-2019 07:10 AM

    How can one apply this solution to a measure that references two additional measures, as shown below?

     

    Lost Customers = IF([Total Orders]>0 && [Last Period Orders]<=0 ,1,0)
     
    For your reference, below are the details of the two additional measures:
    Total Orders = SUMX(RELATEDTABLE('Mfg Invoice Data-Mar'),'Mfg Invoice Data-Mar'[Quantity])
     
    Last Period Orders = CALCULATE(
    SUM('Mfg Invoice Data-Mar'[Quantity]),
    DATESBETWEEN(
    'Mfg Invoice Data-Mar'[Creation Date].[Date],
    DATEADD(LASTDATE('Mfg Invoice Data-Mar'[Creation Date].[Date]),-1*[Selected Period],DAY),
    LASTDATE('Mfg Invoice Data-Mar'[Creation Date].[Date])
    )
    )
     
     
    I am trying to show a total of Lost Customers but the total is outputting "1".
    Message 14 of 55
    14,587 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices