measures
316 Topics
Count of one column based on distinct count of another column being greater than 1
Hi This is probably a basic query but i'm having no luck. I need a measure to count the number of accounts where the sum of distinct count of products they buy is greater than 1. Distinct count as the customer could buy the same product various times within the date range they select. Basically i need to display the number of customers who buy more than product. So the results of the below would be 2. Customers 005 is in there twice buy just bought the same product in two different orders. DATA TABLE ACCOUNT NUMBER PRODUCT 001 A 001 B 002 A 002 B 002 C 003 A 004 A 005 A 005 A Appreciate any help. ThanksSolved702Views0likes2Comments
Help Managing Dynamic Thresholds (Acceptable error rates) in Power BI
Hi everyone, I'm developing a Power BI report to track manufacturing defects for my organization. We use a defect rate threshold that updates periodically based on trending data. I've attached a sample file that reflects the general structure of our data—it's not an exact match, but it's close. I already have measures in place to calculate both the threshold and the error rate. The challenge I’m facing is how to manage updates to the threshold over time. Specifically, I’m trying to figure out how to: Avoid updating thresholds directly in the source table, since that would affect historical data. Apply logic that allows one set of thresholds for historical data and another for data after a threshold update. I’d really appreciate any guidance on how to accomplish this in Power BI. Note: The file includes a "Proposed Error Threshold" table. This is not the format currently used in the model, but I would prefer a solution that uses it, if possible. Thanks in advance for your help! Sample FileSolved1.3KViews0likes6Comments
Sum Certain Rows but Keep in same Column
I have a table like this: Product Totals Data1 20 Data2 10 Product1 50 Product2 100 How could I add the Data1 and Data2 rows together and keep it in the same Product column so it's like this: Product Totals Data 30 Product1 50 Product2 100Solved714Views0likes2Comments
Get the value for sub category by certain %
Hi all, I would like to get the total sales of subcategory where the main category is more than certain % I would like to show all the sales of sub category where the total weightage is more or equal to 30%. for example, the main category that is more than 30% are "Clothes 2" and "Car" and now, I want to get all details of item in sub category which are under "Clothes 2" and "Car". How can I solve this by creating the measure in power bi ? Thanks in advance.Solved457Views0likes2Comments
Issues with Totals in Report Builder When Using DIVIDE and COUNTDISTINCT Measures
Hi, I've connected an RDL file in Report Builder to a semantic model developed in Microsoft Fabric. Basic measures like SUM work as expected. However, I'm encountering issues with more complex measures such as DIVIDE(Sum1, Sum2) and COUNTDISTINCT(ID), particularly in the total rows. When I don't apply a specific formula in the report, the total row displays First(measure) instead of recalculating the total as needed. For the DIVIDE issue, I understand that applying a formula directly in Report Builder can resolve it, which is acceptable. However, I have several complex measures to work with, like COUNTDISTINCT(ID), where retrieving all ID values would exceed my Fabric capacity, making it impractical to perform a distinct count directly in the report. Is there a feasible solution for this? Do you have any advice on handling such scenarios? Additionally, are there alternative tools for creating custom tables for export purposes, other than Power BI, which tends to slow down with complex measures and large datasets?Solved1.7KViews0likes3Comments
Measure Help
I am trying to create a measure counting any carrier that only shipped one order. for example: carrier ID volume(orders) carrier1 200 carrier2 1 carrier3 20 carrier4 13 carrier5 1 the # from this example would be 2 carriers shipped one time. I'm a little new to power Bi so bear with me. This is what I did but it just counted all the carriers: One & Done = CALCULATE(DISTINCTCOUNT(McLeodIQ_Operations[Carrier.Carrier ID]),FILTER(McLeodIQ_Operations,McLeodIQ_Operations[Orders]=1))Solved1.8KViews0likes12Comments
Measure referencing other measures returns different result than when referencing local variables
The following DAX returns the correct values for result1 in all contexts, including the total. For result2, the results are correct in the row context; for example if I create a visual with rows by 'Item'[Label]. However, the total amount does not match result1. result1 references other measures with the same exact definition as their VAR counterparts here. result2 uses the VARs defined here. I obvisously am missing something with context, or perhaps need some CALCULATE? However, I've tried adding CALCULATE to various places here, as well as in the standalone measures and it did not help. How do I get result2 to produce the same number as result1 in all contexts? Consolidated 9L ANA-1542(Consolidated) = VAR Consensus = SUMX ( FILTER( '_Item Budgets', RELATED('Item Budget Name'[Item Budget Status])="Under Development" ), '_Item Budgets'[Quantity 9L] ) VAR WSfinance = SUMX ( FILTER( '_Item Budgets', RELATED('Item Budget Name'[Item Budget Status])="Released" && RELATED('Nav Department'[Dept Code])="20" ), '_Item Budgets'[Quantity 9L] ) VAR DTCfinance = SUMX ( FILTER( '_Item Budgets', RELATED('Item Budget Name'[Item Budget Status])="Released" && RELATED('Nav Department'[Dept Code]) IN { "35", "40", "45", "50" } ), '_Item Budgets'[Quantity 9L] ) VAR result1 = SUMX( SUMMARIZE( '_Item Budgets', 'Item'[Label], "xxx", IF( ISBLANK([Consensus]), [WSfinance], [Consensus] ) + [DTCfinance] ),[xxx] ) VAR result2 = SUMX( SUMMARIZE( '_Item Budgets', 'Item'[Label], "xxx", IF( ISBLANK(Consensus), WSfinance, Consensus ) + DTCfinance ), [xxx] ) RETURN CONCATENATE(format(round(result1,2),"Standard"), CONCATENATE(" | ", format(round(result2,2),"Standard")))Solved662Views0likes3Commentscreate measures with filters
Hello, I need to create measures using some filter to be able to make some card visuals. here is what I did: I have my date base with all years information, so for making the "sales per year" i made to measures: 2025 = SUM(...) and filtered in 2025 poryjected sales. 2024 = CALCULATE([Facturación],SAMEPERIODLASTYEAR('Calendar'[Date])) - I created a measure of get the sales of previous year. than I made a measure to get the difference between both 2024 adn 2025 sales. WHAT I SHOULD DO: I made to charts one "increasing of sales" detailed all the "clientes" that has a positive result and I was using the filter panel. and another of "lost sales" with all the negative results. I need to add some cards to summarize the totals. I have one showing the total of sales in 2025 I need a card showing the total of increaisng sales that is "2,066,314" and a card showing the total of lost sales that is "-919,865" thank you!Solved760Views0likes2CommentsMeasures using filters of negative and positive results
Hello, would like to know how can I make measures with calculations but using filters. here is an example of what I pretend to do I have the sales per country on 2023 and 2024. I am comparing them. so I was thinking to make a measure to show only the countrys that has losing of sales . and another measure that let me show only the countries having an increase of sale with no return of the negative numbers. I think I need to make measures because at the end I need to make a comparative chart showing the losing sales and the increase of sales with the sales of previous year. COUNTRY 2023 2024 Difference USA $5,000 $4,300 -$700 FRANCE $5,000 $5,000 $0 GERMANY $3,500 $2,000 -$1,500 MEXICO $600 $1,200 $600 ITALY $5,500 $5,300 -$200 CHINA $5,000 $3,000 -$2,000 JAPAN $5,000 $4,580 -$420 AUSTRALIA $6,000 $6,100 $100 TOTAL $35,600 $31,480 -$4,120 NEW MEASURES Lost Sales Increase of sales: USA -$700 MEXICO $600 GERMANY -$1,500 AUSTRALIA $100 CHINA -$2,000 $700 JAPAN -$420 -$4,620 FINAL CHART I NEED TO ACHIEVE 2023 LOST SALE INCREASE OF SALES $35,600 -$4,620 $700 thank you!Solved668Views0likes2CommentsCreate YoY Measure without error A date column containing duplicate dates was specified in the call
I am a novice at PowerBI and I'm very confused with measures and dates at the moment so any guidance would help. I'm trying to create a few year over year charts/tables that are counting the number of items created by the date created. This is what I have right now: A date field Created On A title field Title A measure ItemCount = COUNT(table[Title]) A measure ItemCount LY = CALCULATE(COUNT(table[Title]), DATEADD(table[Created On], -1, YEAR)) I then tried to check if my measures worked by creating a table. I put the Created On field with only the year in the date hierarchy and the measure ItemCount. That came out fine. But when I tried to add ItemCount LY I received the error "A date column containing duplicate dates was specified in the call to function 'DATEADD'" I researched this error and I understand that measures need aggregated data to work and I guess the created on field that I was referencing has multiple items with the same date. But I don't understand how to proceed. How can I get unique dates? I've looked up functions that could retrieve only unique dates, or I've seen people suggest creating a date table, but this report is not using a directquery connection. So my ability to make new columns and tables is disabled which seems to be hindering me a lot. I can only create measures. Is there any way forward?Solved1KViews0likes5Comments
