Forum Discussion
Stuznet
Helper V
Helper VSum specific Rows Then Divide Sum of One Column by Sum of Another Column
I'm struggling with a simple Sum and divide. How do I write a Total% measure and turn this Excel formula to DAX? I've tried this function but I'm not getting the correct result Col1 = CAL...
Ashish_Mathurv-lili6-msftthank you so much for your help :) , unfortunately I could not utilize the functions you provided. I ended up with the Variables statement instead and much tideous.
Measure 4 = VAR Col1_Apr = CALCULATE(COUNTA(Table1[ID]),FILTER(Table1, Table1 [Data]="March"),FILTER(Table1, Table1 [S Month]="April"),FILTER(Table1, Table1 [S Year]="2018")) VAR Col1_May = CALCULATE(COUNTA(Table1[ID]),FILTER(Table1,Table1[Data]="April"),FILTER(Table1,Table1[S Month]="May"),FILTER(Table1,Table1[S Year]="2018")) VAR Col2_Apr = CALCULATE(COUNTA(Table1[ID]),FILTER(Table1,Table1[Data]="May"),FILTER(Table1,Table1[S Month]="April"),FILTER(Table1,Table1[S Year]="2018")) VAR Col2_May = CALCULATE(COUNTA(Table1[ID]),FILTER(Table1,Table1[Data]="June"),FILTER(Table1,Table1[S Month]="May"),FILTER(Table1,Table1[S Year]="2018")) RETURN (Col2_Apr + Col2_May ) / (Col1_Apr + Col1_May)
Total Start% = SWITCH(TRUE(), MAX(MonthTable[Month]) = "April", CALCULATE([Measure4]), MAX(MonthTable[Month]) = "May", CALCULATE([Measure5]), MAX(MonthTable[Month]) = "June", CALCULATE([Measure6]))
Ashish_Mathur
Super User
Super UserHi,
What do Col1 and Col2 represent? Are years? If yes, then which years? If not, then for which year is this data?
StuznetHelper V
Helper VHi Ashish_Mathur Col1 and Col2 represent count of IDs
- Ashish_Mathur
I understand that. But that has to for year(s), Cities(s), Stores(s). Please clarify.
- Stuznet
Ashish_Mathursorry, lets use count of Cities
- Ashish_Mathur