Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I wasn't exactly sure how to word my problem to make it stand out from similar but different issues. Anyway, I have a DAX table that I created and it works flawlessly but I was recently asked if I could add an additional column, a unique ID, to the table but when I did, it throws my StDev off, therefore throwing off my Z-Score and I'm not sure how to fix it.
Below is the Expected vs Incorrect output columns.
The measure I'm using for the Expected Output visual is as follows:
StDev_24_TEST =
CALCULATE(STDEV.P('24MonthTable'[Failures]),
DATESINPERIOD('24MonthTable'[Month-Year],
LASTDATE('24MonthTable'[Month-Year])-1,
-24, Month
)
)
I tried using the same measure for my "duplicate" table but it returned all 0's. I tried several different methods and all but one variation returned 0.0. The measure that "somewhat" worked, and I use that term very loosely was:
STD24 =
VAR s =
CALCULATE(
STDEV.P('TEST'[Failures]),
ALL('TEST'), -- Remove any other filters on 'TEST'
DATESINPERIOD(
'TEST'[Month-Year],
LASTDATE('TEST'[Month-Year]) - 1,
-24,
MONTH
)
)
RETURN
s
The ONLY difference between the tables is one column, [PR ID] which I was planning on using to link to other tables because it's a unique identifier.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Ive uploaded a pbix file with sample data to my dropbox. Hope this helps.
StDev_SampleData.pbix
In your TEST table each failure has its own row, therefore the standard deviation is computed across all these 1 values. You need to use STDEVX.P rather than STDEV.P
Having said that I can only get half of it to work.
That's more than where I was able to get so I appreciate it. I'll have to talk to my end user and get their thoughts. if the Product Family will be in a visual or not. I know it's used as a filter sometimes but not always.
What if there was a sum measure for the Failures in the test table and do the stdevx against a measure?
I think that should work, as long as that measure evaluates for each row of the table argument in STDEVX.P . The trick here is to present a suitable table - which can also be a table variable.
I appreciate the insight.....and I need to see what's going on, your suggestion worked for the data I provided (hard-coded in query) but once put in with live data, it throws different values, even when filtered, and all data types are the same. Either way, I appreciate the help and quick responses.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
20 | |
7 | |
6 | |
5 | |
5 |
User | Count |
---|---|
26 | |
10 | |
10 | |
9 | |
6 |