Forum Discussion

chrisread9907's avatar
chrisread9907
Frequent Visitor
10 years ago

SUMIF and VLOOKUP within BI

Hi,   I'm new to power Bi and trying to manipulate some data from my SQL database.   This is a screenshot of the data tables im trying to work on:     What i am looking to do is do a SUM...
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

Create a measure like:

 

SumDurationInSeconds = SUM([DurationInSeconds])

Then, create another measure like this:

 

ConditionalSumDiS = CALCULATE(SumDurationInSeconds, [channel] = "1" & [error] = "False" & [slaveid] = "1" & [dateEnd] = TODAY())

Something like that.

  • chrisread9907's avatar
    chrisread9907
    Frequent Visitor
    10 years ago

    Thanks very much,

     

    I have tried to add both measures but get this messgae:

    Have i done something wrong?

     

    How do i use a measure with graphical reports? can i just drag it out to create a graph etc?

     

    Thanks,

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      10 years ago

      Sorry, my bad, you need to add a aggregation function when referencing columns in a measure (SUM, AVERAGE, etc.) Let me review once again what you are trying to accomplish and see if you can achieve that via this method or if we need to go another route. That's what I get for trying to multitask.

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        10 years ago

        Let's go a different route, it's a little ugly but should accomplish what you want.

         

        Create a new calculated column:

         

        MyDurationInSeconds = IF([channel] = "1",IF([error] = "False",IF([slaveid] = "1" ,IF([dateEnd] = TODAY(),[DurationInSeconds],0),0),0),0)

        Then, you can create a measure to just sum "MyDurationInSeconds".