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

    06-14-2021 16:58 PM

    fcec703
    Regular Visitor
    77868 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,308 Views
    93
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    hamedelbhrawy
    hamedelbhrawy
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-10-2023 01:51 AM
    its retrun wrong rwo Total how to solve  ? 
    
    TotalSales =
    
    VAR Sales2022H1 = SUM('RecieptDetials 2022 H1'[Net amount])
    
    VAR Sales2022H2 = SUM('RecieptDetials 2022 H2'[Net amount])
    
    VAR Sales2023H1 = SUM('RecieptDetials 2023 H1'[Net amount])
    
    VAR Sales2023H2 = SUM('RecieptDetials 2023 H2'[Net amount])
    
    
    
    RETURN
    
    CALCULATE(
    
        Sales2022H1 + Sales2022H2 + Sales2023H1 + Sales2023H2
    
    )
    
    
    
    Message 55 of 55
    1,629 Views
    0
    Reply
    scondon1985
    scondon1985
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-06-2023 10:24 PM

    Hi @Greg_Deckler , thanks for all your postings! I tried the below in the attached project (page 2).

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

     

    My table is DailyBalance, grouping by Date, summing the value LatestValue. Shouldn't my "Test" column be showing the total of LatestValue by day instead of the same value as LatestValue?  

    scondon1985_0-1699337975432.png

     

     

    OperatingParamsSCONDON.pbix
    Message 54 of 55
    1,958 Views
    0
    Reply
    stitch_es
    stitch_es
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-25-2023 08:57 AM

    This looks like something I could use - but I'm a newbie Power BI analyst.  If I have a matrix with multiple levels, how would I apply this to the subtotals?  See screenshot  I have a measure that determines if the lineitem should be displaying a max or a sum - but the subtotal is totaling everything, not just the max it displays

    stitch_es_0-1698249445733.png

     

    Message 49 of 55
    3,044 Views
    0
    Reply
    terryh1
    terryh1
    New Member
    In response to stitch_es
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-25-2023 08:14 PM

    Hi, the issue of totals when using functions other than sum has been around for a while , and is basically due to the different views on what to do with the total in such instances:

    should it be:

    1.a max of all the single lines ie returns a value you will already have in on of your rows. (the default behaviour)

    2. a sum of all the previous max values shown in rows (i think the desired behaviour a lot want to see)

    The simplist way in a simple two tier matrix as shown to deal with it is the use of the Sumx function with the Max being done at each row level., the table referred to is the fields in the matrix. A very good explanation of how to do this is https://youtu.be/yw0QHu9V4UQ?si=YqMmQV7zMNvvjH1-  

    You can also use the inscope function to drive a switch function , where you basically test if you are on a total line ,see this vid https://youtu.be/1tdufLz7_0U?si=9Amxsr2x4zCSAAv5

    but yes not as easy as it should be..

     

    Enjoy

     

    Message 53 of 55
    2,955 Views
    1
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to stitch_es
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-25-2023 09:19 AM

    @stitch_es Well it definitely depends on the circumstance but the general solution is to use SUMMARIZE and as the groupings, include the Rows in your matrix but the grouping will be different depending on where you are in your hierarchy. This additional article should help with that:

    Matrix Measure Total Triple Threat Rock & Roll - Microsoft Fabric Community


    @ 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 50 of 55
    3,033 Views
    0
    Reply
    stitch_es
    stitch_es
    Regular Visitor
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-25-2023 01:59 PM

    Thank you for responding so quickly @Greg .  Maybe you can direct me in where I'm going wrong?  I have a measure that determines if the row should show a sum of line items or a max- that's working (max vs sum), and then i created a measure using your suggestion here for the totals to take that max and use that to sum it up - but it doesn't seem to be working, it just sums up as if the max is not part of the equation.  See screenshots for explanation.

    stitch_es_1-1698267474984.png

     

    stitch_es_0-1698267452098.pngstitch_es_2-1698267537183.png

     

    Message 51 of 55
    2,996 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to stitch_es
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-25-2023 03:37 PM

    @stitch_es Hard to know for sure without sample data to play with but it seems like you are not summarizing correctly. I would think you would need at least 2 groupings, ParentCategory and LineItemName.


    @ 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 52 of 55
    2,983 Views
    0
    Reply
    AmitWaware
    AmitWaware
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-14-2023 06:40 AM

    Hi @Greg_Deckler ,


    Can you please help me understand why Totals are wrong in a Matrix below .

    I am attaching .pbix file also

    AmitWaware_0-1686761911907.png

     

    In above matrix i am spreading value of each NewVal  column till the date of Next NewValue,I got the expected spread results 

    But ......Totals are Wrong.....

    Below is The measure 

    Spread Value =

     Var val=    CALCULATE(count(Data[NewValue]),ALLEXCEPT(DimDate,DimDate[Date]))
                        
     var MinDate =CALCULATE(MIN(Data[CreatedDate]),
                            ALLEXCEPT(Data,Data[ProjectName],Data[Index]))
    var MaxDate =CALCULATE(MAX(Data[EndDateFinal]),
                            ALLEXCEPT(Data,Data[ProjectName],Data[Index]))
    return  
                     IF(HASONEVALUE(Data[CreatedDate]),COUNTX(KEEPFILTERS(Data),val),
               IF(HASONEVALUE(DimDate[Date])=FALSE() && max(DimDate[Date])>MaxDate,BLANK(),

              IF(HASONEVALUE(DimDate[Date])=FALSE() && MIN(DimDate[Date])>MinDate,Val

                    )))

    Total_matrix_Community.pbix
    Message 48 of 55
    12,083 Views
    0
    Reply
    canddig
    canddig
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-21-2022 05:12 AM

    Hi @Greg_Deckler ,

     

    This is awesome and I think would solve my problem that I am having but I am new to writing dax and the forumla I am trying to configure is already very.... busy. Could you take a look and help guide me toward a solution that would work? 

     

    RVU % = IF(and(sum(Summary[25th % tile PTD])=0,sum(Summary[wRVU PTD])=0),0,IF(sum(Summary[25th % tile PTD])=0,0,IF(sum(Summary[wRVU PTD])<=sum(Summary[25th % tile PTD]),sum(Summary[wRVU PTD])/sum(Summary[25th % tile PTD])*0.25,IF(sum(Summary[wRVU PTD])<=sum(Summary[50th % tile PTD]),((sum(Summary[wRVU PTD])-sum(Summary[50th % tile PTD]))/(sum(Summary[50th % tile PTD])-sum(Summary[25th % tile PTD]))*0.25)+0.25,IF(sum(Summary[wRVU PTD])<=sum(Summary[75th % tile PTD]),((sum(Summary[wRVU PTD])-sum(Summary[50th % tile PTD]))/(sum(Summary[75th % tile PTD])-sum(Summary[50th % tile PTD]))*0.25)+0.5,IF(((sum(Summary[wRVU PTD])-sum(Summary[75th % tile PTD]))/(sum(Summary[90th % tile PTD])-sum(Summary[75th % tile PTD]))*0.15)+0.75>0.99,(((sum(Summary[wRVU PTD])-sum(Summary[75th % tile PTD]))/(sum(Summary[90th % tile PTD])-sum(Summary[75th % tile PTD]))*0.15)+0.75),((sum(Summary[wRVU PTD])-sum(Summary[75th % tile PTD]))/(sum(Summary[90th % tile PTD])-sum(Summary[75th % tile PTD]))*0.15)+0.75))))))
     
    It calculates correctly on the row level but same issue as you stated the totals are very messed up and they are meant to be a percentage. Any guidance, insight or thoughts? 
     
    Thanks so much in advance, 
    Candace 
    Message 47 of 55
    35,021 Views
    0
    Reply
    Power_Chum
    Power_Chum
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-26-2022 10:12 PM

    @Greg_Deckler I feel like Salieri reading Mozart's sheet music. I regret that I have but one kudos to give for my... Deckler? This is some powerful dark magic you got here. Thanks for sharing.

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

    ‎03-03-2022 12:44 AM

    Hi @Greg_Deckler, 

    I have this table: 

    Loladba_0-1646296653500.png

    In the second column i did the MAX function and I was trying to get the sum of that column

    I tried what you said:

    m_Total =
    VAR __table = SUMMARIZE('Documents',[No-QR7],"__value",[max nb pages])
    RETURN
    IF(HASONEVALUE(Documents[No-QR7]),[max nb pages],SUMX(__table,[__value]))
     
    but it doesn't give the correct sum, it supposed to be 1034 but it gives 964 (it looks like it stops adding after the 4 9s.)
     
    Can you help me please?
    Message 44 of 55
    48,948 Views
    0
    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

    ‎03-03-2022 04:28 PM

    @Anonymous My guess is that it is perhaps removing duplicates. For example, I see in your image that you have 13-7965/7966/7967 listed twice so do you want that counted twice? If you use SUMMARIZE, that returns distinct values and then if you use MAX you would only get one 6, not two. Otherwise, you would want to use CONCATENATEX on your __table variable in your return statement to get a list of what all is in that table so that you can see what is going on.


    @ 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 45 of 55
    48,864 Views
    0
    Reply
    Jdpatino21
    Jdpatino21
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-04-2021 12:54 PM

    Hello! @Greg_Deckler  I have a slighty different issue I have the following:
    I´m trying to recreate a forecast based on YTD actual values and budget to complete, so I have a couple of tables:

     

    Fact tables:

    Presupuesto "Budget"

    contabilidad Balance_Prueba "actual"
    Ajustes Forecast "forecast Adjusments"
    'Ajustes EEFF' "Financial Adjustments"
     
    So, what I´m tryign to do is, that if there is not an "actual" well get the forecast.... on this "m_single" measure
     
    Jdpatino21_0-1633377172993.png

     

    And then I did the "m:total" version

    Jdpatino21_1-1633377212740.png

     

    However, when I try them on a table..... it get this result

     

    Jdpatino21_2-1633377250727.png

    Nor the single o the total  are summarizing correctly.

     

    can you help me?

    Message 42 of 55
    65,618 Views
    0
    Reply
    mdarlington
    mdarlington
    Frequent Visitor
    In response to Jdpatino21
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-20-2022 05:52 AM

    Hi,

    Was just wondering if you have found a solution to this.?  I too have a forecasting measure and cannot get the correct sum.

    Message 43 of 55
    39,395 Views
    0
    Reply
    tahar1407
    tahar1407
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-04-2021 06:05 AM

    Hi,

    I have an issue with summarizing multiple measures. This is my Measure :
    Somme des mesures = CALCULATE(

      SUMX(VALUES('Calendar'[Add on]),'Calendar'[Add on])

    + SUMX(VALUES('Calendar'[Indirect Manufacturing]),'Calendar'[Indirect Manufacturing])

    + SUMX(VALUES('Calendar'[Royalties]),'Calendar'[Royalties])

    + SUMX(VALUES('Calendar'[Overhead]),'Calendar'[Overhead])

    + SUMX(VALUES('Calendar'[Bank position]),'Calendar'[Bank position]))

     

    And this is my cumulative Measure :

    Cumulative Total Measure = CALCULATE([Total measure 1],

        FILTER(ALLSELECTED('Calendar'),'Calendar'[Week 3] <= MAX('Calendar'[Week 3]) && 'Calendar'[Week 3] >= DATE(2021,07,17))

    )

     

    It works properly for some rows but it stopped then :

    Hi,

    I have an issue with summarizing multiple measures. This is my Measure :
    Somme des mesures = CALCULATE(

      SUMX(VALUES('Calendar'[Add on]),'Calendar'[Add on])

    + SUMX(VALUES('Calendar'[Indirect Manufacturing]),'Calendar'[Indirect Manufacturing])

    + SUMX(VALUES('Calendar'[Royalties]),'Calendar'[Royalties])

    + SUMX(VALUES('Calendar'[Overhead]),'Calendar'[Overhead])

    + SUMX(VALUES('Calendar'[Bank position]),'Calendar'[Bank position]))

     

    And this is my cumulative Measure :

    Cumulative Total Measure = CALCULATE([Total measure 1],

        FILTER(ALLSELECTED('Calendar'),'Calendar'[Week 3] <= MAX('Calendar'[Week 3]) && 'Calendar'[Week 3] >= DATE(2021,07,17))

    )

     

    It works properly for some rows but it stopped then :

     

    I 

     don't understand what's wrong and why my measure stopped 

     

    Could you please help me

     

    Thank you so much

     

    tahar1407_0-1628082277563.png

    I 

     don't understand what's wrong and why my measure stopped 

     

    Could you please help me

     

    Thank you so much

    Message 39 of 55
    72,432 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to tahar1407
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-04-2021 06:18 AM

    @tahar1407 Super difficult to understand without sample data and expected output. Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.


    @ 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 40 of 55
    72,426 Views
    1
    Reply
    anushaghi123
    anushaghi123 Helper I
    Helper I
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-12-2023 06:55 AM

    Hi @Greg_Deckler ,

    I have created the quick measure to calculate the moving average with dynamic slicer. But facing column total error for the measure. Like it has to sum Jan to Dec data and show it in the column but column total only taking Dec month data everytime.

     

    Example - Should be Oct+Nov+Dec = 75379+62685+26346 = 164410 But the total showing is 26346.

     

    Please help me with the following issue

    I am getting column total error and taking Matrix table 

    Rows - Country,ProductCategory

    Column - Month

    anushaghi123_0-1694526687167.png

     

     

    I'm getting column total incorrect for Forecast(Blue) data.

     

    Dax Formula (Quick Measure) - Product_Count rolling average =
    IF(
    ISFILTERED('Table_name'[dimdate]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    VAR __LAST_DATE = ENDOFMONTH('Table_name'[dimdate].[Date])
    VAR __DATE_PERIOD =
    DATESBETWEEN(
    'Table_name'[dimdate].[Date],
    STARTOFMONTH(DATEADD(__LAST_DATE, 'Moving Average'[Moving Average Value], MONTH)),
    __LAST_DATE
    )
    RETURN
    AVERAGEX(
    CALCULATETABLE(
    SUMMARIZE(
    VALUES('Table_name'),
    'Table_name'[dimdate].[Year],
    'Table_name'[dimdate].[QuarterNo],
    'Table_name'[dimdate].[Quarter],
    'Table_name'[dimdate].[MonthNo],
    'Table_name'[dimdate].[Month]
    ),
    __DATE_PERIOD
    ),
    CALCULATE(
    SUM('Table_name'[Product_Count]),
    ALL('Table_name'[dimdate].[Day])
    )
    )
    )

     

    Thanks!

    Message 41 of 55
    6,146 Views
    0
    Reply
    fcec703
    fcec703
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-14-2021 04:58 PM

    What is the syntax for this approach in Excel2013, i.e. without declaring a variable?

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

    ‎04-08-2021 08:29 AM

    Found this just in time, and saved my laptop from being smashed into pieces. Thank you good sir.

    Message 37 of 55
    85,674 Views
    0
    Reply
    shivakoriginja
    shivakoriginja
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎03-18-2021 10:01 PM

    Hi @Greg_Deckler,
    I am not getting 100% on the table when I calculate the utilization %.
    I have created a column:

    Utilization  = DIVIDE(CALCULATE(SUM(Utilisation_SAC[Total Hours])-SUM(Utilisation_SAC[Hours])),SUM(Utilisation_SAC[Total Hours])).

    sample.PNG

     please help me out.



     

     


     

     

    Message 34 of 55
    88,158 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