Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help with DAX formula?

Hello,

 

I have the following table:

 

Product  Period  Quantity

Apples    Jan        10

Apples    Feb        20

....

Oranges  Jan        20

Oranges  Feb       10

...

 

And I have this summary table for periods, where I want to see if Apples are greater than oranges for each period.

 

Period   Apples>Oranges?

Jan              No

Feb             Yes

...                 ...

 

Any suggestions on best way to do it?

 

Thanks.

 

  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Anonymous - Well you could put Period in a table visualization along with this measure:

    Measure =
  VAR __Apples = SUMX(FILTER('Table',[Product]="Apples"),[Quantity])
  VAR __Oranges = SUMX(FILTER('Table',[Product]="Oranges"),[Quantity])
RETURN
  IF(__Apples > __Oranges,"Yes","No")

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Anonymous - Well you could put Period in a table visualization along with this measure:

    Measure =
  VAR __Apples = SUMX(FILTER('Table',[Product]="Apples"),[Quantity])
  VAR __Oranges = SUMX(FILTER('Table',[Product]="Oranges"),[Quantity])
RETURN
  IF(__Apples > __Oranges,"Yes","No")
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , Create a measure like

    if(calculatet(sum(table[Quantity]), filter(Table, Table[Product] ="Apples")) -calculatet(sum(table[Quantity]), filter(Table, Table[Product] ="Oranges")) >0 , "Yes", "No")

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi @ Anonymous ,

    According to my understanding, you want to specify whether  Apples’quantity is greater than Oranges’, right?

     

    You could use the following formula:

    Apples>Oranges? =
VAR _minus =
    CALCULATE (
        SELECTEDVALUE ( 'Great'[Quantity] ),
        ALLEXCEPT ( Great, Great[Period] ),
        'Great'[Product] = "Apples"
    )
        - CALCULATE (
            SELECTEDVALUE ( 'Great'[Quantity] ),
            ALLEXCEPT ( Great, Great[Period] ),
            'Great'[Product] = "Oranges"
        )
RETURN
IF ( _minus > 0, "Yes", "No" )

    My visualization looks like this:

    Is the result what you want? If you have any questions, please upload some data samples and expected output.

    Please do mask sensitive data before uploading.

     

    Best Regards,

    Eyelyn Qin

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    Did I answer your question ? Please mark my reply as solution. Thank you very much.

    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,

    Eyelyn Qin