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
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
    • Instructor-led training
    • Getting started
      • Overview
      • Online workshops
      • 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
    • 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
    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: 
    • Microsoft Power BI Community
    • 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
    56266 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 46
    129,582 Views
    80
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    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 46 of 46
    13,419 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 45 of 46
    20,307 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 43 of 46
    27,346 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 44 of 46
    27,262 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 41 of 46
    44,016 Views
    0
    Reply
    mdarlington
    mdarlington
    New Member
    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 42 of 46
    17,793 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 46
    50,830 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 46
    50,824 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 46
    56,266 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 46
    64,072 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 46
    66,556 Views
    0
    Reply
    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 46
    66,090 Views
    0
    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 46
    66,083 Views
    0
    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 46
    74,777 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 46
    74,627 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 46
    74,586 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 46
    75,023 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 46
    75,324 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 46
    75,325 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 46
    75,125 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
    California Consumer Privacy Act (CCPA) Opt-Out Icon Your California Privacy Choices