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

Divide by row in DAX

Hi, 

I would like help writing a DAX measure to divide by each row. 

I want to know what % of students did not submit an assignment. 
The issue comes in because we need to group the data by "Area" and "Term". 
There are two "Terms" either Fall 2020 or Fall 2021. 

Fortunately, there is a "% No Subs" calc column that gives a guideline of what I need the answers to look like. 


Please see table below for an example: 

Nateintern_0-1633021558063.png

There is a calculated column called "% No Subs". 
That was calculated by dividing #! Submitted by Max Enrol. 
However, I would like to create a measure that does this exact thing because measures are better than calc columns. 

Here is what I have tried: 
1. 

Rate =
SUMMARIZE('No Submissions Data','No Submissions Data'[Area],"Rate",SUMX (
'No Submissions Data',
DIVIDE('No Submissions Data'[# ! Submitted],'No Submissions Data'[Max Enrol])
))
2. 
Difference =
VAR f20 = CALCULATE('No Submissions Data'[No Submissions Rate],'No Submissions Data'[Term] = "Fall 2020")

RETURN

VAR f21 = CALCULATE('No Submissions Data'[No Submissions Rate],'No Submissions Data'[Term] = "Fall 2021")

RETURN

F21 - F20
 
TIA
3 REPLIES 3
m3tr01d
Continued Contributor
Continued Contributor

If you put Area and Term on the rows with the measure that @PaulOlding provide will work.
If it doesn't give you the result you are expecting, you'll need to provide us with more information. 
Example of the calculation, example of visual ...

PaulOlding
Solution Sage
Solution Sage

Hi,

 

How about...

Rate =
VAR _NoSub = SUM('No Submissions Data'[# ! Submitted])

VAR _Enrol = SUM( 'No Submissions Data'[Max Enrol])

RETURN

DIVIDE(_NoSub, _Enrol)

Anonymous
Not applicable

Hi Paul, 

That won't give an accurate result. 

Each area has a different denominator (Max Enrol). 
Dividing by the total sum is will give the wrong answer to the question at hand. 

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.

Top Solution Authors