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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
boyddt_mn
Helper III
Helper III

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 III
Helper III

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 III
Helper III

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.