Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |