Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there. I'm new to PowerBi and especially DAX. Need some help to transform some SQL syntax to DAX.
I'll try a simplifyed example(my data has several weeks and teams). If I have data like this:
I want to group by Calendarweek, Team, and the calculate the part/percentage of all rows. So NPS_Type = 3 will be 9/15 = 60,0%, and NPS_Type 1 will be 1/15 = 6,7%. The SQL calculation for this is :
SELECT Calendarweek, Team,
(SUM(CASE WHEN [NPS_type] = 3 THEN numberOfRows ELSE 0 END)*1.0/SUM(numberOfRows) * 100) AS PromotorsPrc,
(SUM(CASE WHEN [NPS_type] = 1 THEN numberOfRows ELSE 0 END)*1.0/SUM(numberOfRows) * 100) AS DetractorsPrc
FROM [MyTable]
GROUP BY Calendarweek, Team
How to do this in DAX? I've tried with SUMMARIZE and GROUP BY, but get lost when I try to implement the CASE. Tried SWITCH and IF, can't get the syntax right.
Any suggestions appreciated, thanks.
Solved! Go to Solution.
Hi @JJ_NetCo ,
By my tests and research, you could create the two calcualted columns to achieve your output.
PromotorsPrc = IF ( 'Table_7'[NPS_Type] = 3, 'Table_7'[numberOfRows], 0 ) * 1 / CALCULATE ( SUM ( Table_7[numberOfRows] ), ALLEXCEPT ( Table_7, 'Table_7'[Calendarweek], Table_7[Team] ) ) * 100 DetractorsPrc = IF ( 'Table_7'[NPS_Type] = 1, 'Table_7'[numberOfRows], 0 ) * 1 / CALCULATE ( SUM ( Table_7[numberOfRows] ), ALLEXCEPT ( Table_7, 'Table_7'[Calendarweek], Table_7[Team] ) ) * 100
Then you could get the output below.
Hope this can help you!
Best Regards,
Cherry
Hi @JJ_NetCo ,
By my tests and research, you could create the two calcualted columns to achieve your output.
PromotorsPrc = IF ( 'Table_7'[NPS_Type] = 3, 'Table_7'[numberOfRows], 0 ) * 1 / CALCULATE ( SUM ( Table_7[numberOfRows] ), ALLEXCEPT ( Table_7, 'Table_7'[Calendarweek], Table_7[Team] ) ) * 100 DetractorsPrc = IF ( 'Table_7'[NPS_Type] = 1, 'Table_7'[numberOfRows], 0 ) * 1 / CALCULATE ( SUM ( Table_7[numberOfRows] ), ALLEXCEPT ( Table_7, 'Table_7'[Calendarweek], Table_7[Team] ) ) * 100
Then you could get the output below.
Hope this can help you!
Best Regards,
Cherry
Thank you. Smart solution. That was exactly what I needed.
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |