measures

314 Topics

Mastering Fabric's Metrics Layer
Carly Newsome and V Maharajh, from the Microsoft Fabric product team, will be presenting the transformative new feature designed to redefine how organizations manage and consume metrics. Metrics Hub is an innovative metric layer within Fabric, aimed at helping organizations define, discover, and reuse trusted metrics with ease. This feature allows creators within an organization to develop standardized metrics that incorporate essential business logic, ensuring consistency across the organization. ⁠Introducing Fabric Metrics Layer: A New Era of Metric Management in Fabric (preview)
maggiebeyer
23 days ago Place Health & Life Sciences Fabric User Group
1.2KViews
0likes
0Comments
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. Thanks
Solved
STEVE_WT
10 months ago Place DAX Commands and Tips
708Views
0likes
2Comments
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 File
Solved
scoutmedic
1 year ago Place DAX Commands and Tips
1.3KViews
0likes
6Comments
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 100
Solved
BrianNeedsHelp
1 year ago Place DAX Commands and Tips
722Views
0likes
2Comments
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.
Solved
Anonymous
1 year ago Place DAX Commands and Tips
461Views
0likes
2Comments
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?
Solved
Smackcat
1 year ago Place Report Server
1.7KViews
0likes
3Comments
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))
Solved
mkane12
1 year ago Place DAX Commands and Tips
1.8KViews
0likes
12Comments
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")))
Solved
Karl-D
1 year ago Place DAX Commands and Tips
668Views
0likes
3Comments
create 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!
Solved
Marango
1 year ago Place DAX Commands and Tips
774Views
0likes
2Comments
Measures 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!
Solved
Marango
1 year ago Place DAX Commands and Tips
679Views
0likes
2Comments