Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 Ord | Created on | SDTY | Order value | Curr. | POtyp | Sold-to | Customer Name | City |
300600292 | 1/04/2017 | OR | 4,225.75 | AUD | 570039 | XYZ | ||
300600293 | 1/04/2017 | OR | 2,001.68 | AUD | 570039 | ABC | ||
300600827 | 3/04/2017 | EO | 814.00 | AUD | 364651 | BCD | ||
300600828 | 3/04/2017 | EO | 814.00 | AUD | 593323 | 123 | ||
300601580 | 5/04/2017 | OR | 2,051.82 | AUD | 580064 | JKL |
Any help would be much appreciated,
Thanks
Ifun
Solved! Go to Solution.
@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:
Regards,
Charlie Liao
@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:
Regards,
Charlie Liao
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |