Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Calculating an index from true/false data - SUM.IF formula

Hi    I'm used to apply SUM.IF in Exel but I don't know how do the same via a Measure in Power BI.    I will illustrate my problem by an example. Let's say I have this data:    Sales opportu...
Need Help
Show and Tell
  • Baskar's avatar
    Baskar
    10 years ago

    1. create the conditional filed which i mention in first reply on this post like this.

     

     

    2. Create one calculation or Directly drag the Value measure into the card and choose as Average 

     

     

    3. Darg the final Condtional Column into visual filter  and choose greater than "0" Thats it Dude.

     

     

  • ImkeF's avatar
    ImkeF
    10 years ago

    Yes, Baskar 's solution works fine for that purpose.

     

    But if you're after a more robust solution, this measure would do:

     

    NewAverage = CALCULATE(AVERAGE('Table2'[Revenue]), FILTER(Table2, 'Table2'[Include]=1))

     

    No need to switch to Average in the report-design then.

     

ankitpatira's avatar
ankitpatira
Icon for Community Champion rankCommunity Champion
10 years ago

Anonymous in that case create three measures as below,

 

AvgIfCarHasBackSeats = CALCULATE( (SUM(sam[Revenue]) / COUNt(sam[Revenue])) * 100 , sam[Does the car have backseats?] = "TRUE" )

 

AvgIfCarBlue  = CALCULATE( (SUM(sam[Revenue]) / COUNt(sam[Revenue])) * 100 , sam[Is Car Blue?] = "TRUE" )

 

AvgIfCarIs4WD = Measure = CALCULATE( (SUM(sam[Revenue]) / COUNt(sam[Revenue])) * 100 , sam[Is the Car 4WD?] = "TRUE" )

Anonymous's avatar
Anonymous
Not applicable
10 years ago

ankitpatira Thanks for your answer.

 

From your solution I will up evaluate each one of the values individual.

 

I want to evalualte the row including either one or more of each criteria to be TRUE.

 

BR

Anders

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

    Anonymous Below should work. also ensure column of true / false in power bi desktop are defined as data category Text.

     

     

    Measure = CALCULATE( (SUM(sam[Revenue]) / COUNt(sam[Revenue])) * 100 ,

    OR (

    sam[Does the car have backseats?] = "TRUE",

    sam[car 4wd?] = "TRUE",

    sam[car blue?] = "TRUE"

    ))

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      ankitpatira

      I don't think the OR ( ) function for this works. Am I right?

       

      It dosen't work for me... It says something about "the highest number of arguements for this function is 2" - do I put the seperators wrong?

       

       

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

        Anonymous ok then try with using SWITCH() function. SWITCH will work,