Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
boyddt_mn
Helper IV
Helper IV

converting an excel calculation to DAX

I have a user with a specific request for help converting an Excel spreadsheet into a Power BI report. The data table that he is using is below.

NC Rate Doc = Documentation / Production

3mo Avg is three month rolling average of NC Rate Doc

The Alert is 3mo avg+2*sqrt(3mo avg*(1-3mo avg) / Production)

OR

$E4+2*SQRT($E4*(1-$E4)/$B4)

I was able to find enough information to get the rolling average ( listed below) but the Alert is alluding me

 

Any help is greatly appreciated.

 

 abcdefg
1MonthProductionDocumentationNC Rate Doc3mo AvgAlertAction
2Mar-2111738550.5%   
3Apr-2111389520.5%   
4May-2110720350.3%0.4%0.5%0.6%
5Jun-2112167580.5%0.4%0.5%0.6%
6Jul-2111876620.5%0.4%0.6%0.6%
 Aug-2113589720.5%0.5%0.6%0.7%
 Sep-2112872900.7%0.6%0.7%0.8%
 Oct-2112579510.4%0.5%0.7%0.7%
 Nov-2112523590.5%0.5%0.7%0.7%
 Dec-2112631560.4%0.4%0.6%0.6%
 Jan-2212316630.5%0.5%0.6%0.7%
 Feb-2214158600.4%0.5%0.6%0.6%
 Mar-2211103420.4%0.4%0.6%0.6%

 

NCs R03M =
VAR NumberofMonths = 3
VAR LastSelectedDate = MAX('Datekey'[Date])
VAR Period = DATESINPERIOD('DateKey'[Date], LastSelectedDate, -NumberofMonths, MONTH)
VAR Result =
CALCULATE(
AVERAGEX(
VALUES('DateKey'[YearMonthnumber]),
[NC Count]
),
Period
)
RETURN
Result
1 ACCEPTED SOLUTION
boyddt_mn
Helper IV
Helper IV

I was able to finally sort things out. I was making things much more difficult than they needed to be. This is the measure that I put together.

 

Alert = 
VAR Result =
    [NCs R03M]+2*SQRT(DIVIDE([NCs R03M]*(1-[NCs R03M]),'Key Measures'[Monthly Orders]))

RETURN
    Result

View solution in original post

1 REPLY 1
boyddt_mn
Helper IV
Helper IV

I was able to finally sort things out. I was making things much more difficult than they needed to be. This is the measure that I put together.

 

Alert = 
VAR Result =
    [NCs R03M]+2*SQRT(DIVIDE([NCs R03M]*(1-[NCs R03M]),'Key Measures'[Monthly Orders]))

RETURN
    Result

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.