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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

calculate % of orders received via email vs. electronic by month

Hi, 

Quite new to DAX and trying to calculate % of orders received via email vs. electronic by month.

 

In our system, we have two different document types that represent email/manual orders vs internet orders. My table looks like following, what I am after that to caclulate each month how many orders by % were internet vs. manual.

There are three OR orders out of 5 so 60% manual and remaining 40% internet. How I do that in Power BI using measure? I tried COUNTROWS, COUNT etc but can't filter it out by SDTY 

 

Sales OrdCreated onSDTYOrder valueCurr.POtypSold-toCustomer NameCity
3006002921/04/2017OR4,225.75AUD 570039XYZ 
3006002931/04/2017OR2,001.68AUD 570039ABC 
3006008273/04/2017EO814.00AUD 364651BCD 
3006008283/04/2017EO814.00AUD 593323123 
3006015805/04/2017OR2,051.82AUD 580064JKL 

 

 

Any help would be much appreciated,

 

Thanks

 

Ifun

 

1 ACCEPTED SOLUTION
v-caliao-msft
Employee
Employee

@Anonymous,

 

In your scenario, you want to calculate the percent of the type of SDTY based on each month. Right?

 

To achieve your requirement, you can create a calculated table. And in this calculated table, you can try following DAX script to get the list of all the measures.

 

Please refer:

Test Table = SUMMARIZE( Table2,
Table2[Create on ].[Month],
"TotalMonth",COUNTROWS(Table2),
"TotalOR",CALCULATE(COUNT(Table2[SDTY]),FILTER(Table2,Table2[SDTY]="OR")),
"TotalEO",CALCULATE(COUNT(Table2[SDTY]),FILTER(Table2,Table2[SDTY]="EO")),
"PercentOR",CALCULATE(COUNT(Table2[SDTY]),FILTER(Table2,Table2[SDTY]="OR"))/COUNT(Table2[Create on ].[Month]),
"PercentEO",CALCULATE(COUNT(Table2[SDTY]),FILTER(Table2,Table2[SDTY]="EO"))/COUNT(Table2[Create on ].[Month]))

The result shows like this:
Capture1.PNG

 

Regards,

Charlie Liao

View solution in original post

1 REPLY 1
v-caliao-msft
Employee
Employee

@Anonymous,

 

In your scenario, you want to calculate the percent of the type of SDTY based on each month. Right?

 

To achieve your requirement, you can create a calculated table. And in this calculated table, you can try following DAX script to get the list of all the measures.

 

Please refer:

Test Table = SUMMARIZE( Table2,
Table2[Create on ].[Month],
"TotalMonth",COUNTROWS(Table2),
"TotalOR",CALCULATE(COUNT(Table2[SDTY]),FILTER(Table2,Table2[SDTY]="OR")),
"TotalEO",CALCULATE(COUNT(Table2[SDTY]),FILTER(Table2,Table2[SDTY]="EO")),
"PercentOR",CALCULATE(COUNT(Table2[SDTY]),FILTER(Table2,Table2[SDTY]="OR"))/COUNT(Table2[Create on ].[Month]),
"PercentEO",CALCULATE(COUNT(Table2[SDTY]),FILTER(Table2,Table2[SDTY]="EO"))/COUNT(Table2[Create on ].[Month]))

The result shows like this:
Capture1.PNG

 

Regards,

Charlie Liao

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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