Forum Discussion

Stuznet's avatar
Stuznet
Icon for Helper V rankHelper V
7 years ago
Solved

Sum 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...
Need Help
Show and Tell
Tutorial Requests
  • Stuznet's avatar
    Stuznet
    7 years ago

    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's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

I understand that.  But that has to for year(s), Cities(s), Stores(s).  Please clarify.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      Hi,

       

      You may refer to my solution in this PBI file.

       

      Hope this helps.

       

      • Stuznet's avatar
        Stuznet
        Icon for Helper V rankHelper V
        7 years ago

        Ashish_MathurThank you for sharing your solution but it is almost close enough what I'm looking for. I just don't know how to rewrite your function. 

         

        So here is my function.

        Month Table just like your Month_Order table

        January to December

         

        Measure Col1

        Col1 = SWITCH(TRUE(),

MAX(Table2[Month]) = "April",CALCULATE(
            COUNT(Table1[ID]),FILTER(DATA,[Start Month]="April"
                && [Start Year] = "2018"
                && [Data] = "March")),

MAX(Table2[Month]) = "May", CALCULATE(
            COUNT(Table1[ID]),FILTER(Table1,[Start Month]="May"
                && [Start Year] = "2018"
                && [Data] = "April")),

MAX ( Table2[month] ) = "June", CALCULATE (
            COUNT(Table1[ID]),FILTER (Table1,[Start Month]= "June"
                && [Start Year] = "2018"
                && [Data] = "May")))

        Measure Col2

        Col2 = SWITCH(TRUE(),

MAX(Table2[Month]) = "April",CALCULATE(
            COUNT(Table1[ID]),FILTER(Table1,[Finish Month]="April"
                && [Finish Year] = "2018"
                && [Data] = "May")),

MAX(Table2[Month]) = "May",CALCULATE(
            COUNT(Table1[ID]),FILTER(Table1,[Finish Month]="May"
                && [Finish Year] = "2018"
                && [Data] = "June")),

MAX(Table2[Month]) = "June",CALCULATE(
            COUNT(Table1[ID]),FILTER(Table1,[Finish Month]="June"
                && [Finish Year] = "2018"
                && [Data] = "July")))

        Measure Total% 

        Total % = [Col1Total] / [Col2Total]