Forum Discussion

Evelin_'s avatar
Evelin_
Icon for Helper I rankHelper I
1 year ago
Solved

DAX calculate with multiple criteria

Hello, I would like to have a measure where I want to calculate a share for actual and for Target, and then I want to highlight the difference between them. For the share I have to get from the database 2 VAR, 1 with 1 filter but the other one with 2 filter like this:
 
VAR 1BP=
Calculate(
    SUM(Database[Volume in UC]),
    Filter(
        Database,
         Database[Version] = "BP"
        && Database[xxx] = "SS"
    )
VAR 2BP=
Calculate(
    SUM(Database[Volume in UC]),
         Database[Version] = "BP"
    )
VAR 3 SHARE=
divide (VAR1BP , VAR2BP , -1) ---> here I should get something like 68% (it should be under 100%)
 
VAR 1ACT=
Calculate(
    SUM(Database[Volume in UC]),
    Filter(
        Database,
         Database[Version] = "ACT"
        && Database[xxx] = "SS"
    )
VAR 2ACT=
Calculate(
    SUM(Database[Volume in UC]),
         Database[Version] = "ACT"
    )
VAR 4 SHARE=
divide (VAR1ACT , VAR2ACT , -1) ---> here I should get something like 68% (it should be under 100%)
 VAR 5 = VAR 3 SHARE - VAR 4 SHARE 
 
How Can I write this dax properly? I think I wont have problem with the RETURN part, I have problem with the VAR parts.
Thank you in advance!
 
  • AntrikshSharma's avatar
    AntrikshSharma
    1 year ago

    Evelin_ From the DAX code I can spot couple of mistakes. You are using Database table reference inside CALCULATE which is respecting the existing Filter Context so before it can be filtered for BP and SS it already gets filtered by whatever is the active Filter Context and the table returned by FILTER will be empty, try this instead:

     

    CALCULATE (
    SUM ( Database[Volume in UC] ),
    Database[Version] = "BP"
        && Database[xxx] = "SS"
)

     

    The above will ensure it ignores the existing Filter context on these 2 columns.

     

    And if you don't want to overwrite the existing filter context on Version and XXX then use this:

     

    CALCULATE (
    SUM ( Database[Volume in UC] ),
    KEEPFILTERS ( Database[Version] = "BP" && Database[xxx] = "SS" )
)

     

     

  • FreemanZ's avatar
    FreemanZ
    1 year ago

    hi Evelin_ ,

     

    try like:

    Measure =

    VAR 1BP=

    Calculate(

        SUM(Database[Volume in UC]),

        Filter(

            Database,

             Database[Version] = "BP"

            && Database[xxx] = "SS"

        )

    VAR 2BP=

    Calculate(

        SUM(Database[Volume in UC]),

             Database[Version] = "BP"

        )

    VAR 3SHARE= divide (1BP , 2BP ) - 1

    RETURN 3SHARE

  • Dangar332's avatar
    Dangar332
    1 year ago

    Evelin_ 
    You need to use Dax Formatter 

     

    VAR BP_1 =
    CALCULATE (
        SUM ( Database[Volume in UC] ),
        FILTER ( Database, Database[Version] = "BP" && Database[xxx] = "SS" )
    )
VAR BP_2 =
    CALCULATE ( SUM ( Database[Volume in UC] ), Database[Version] = "BP" )
VAR SHARE_3 =
    DIVIDE ( BP_1, BP_2, -1 )
VAR ACT_1 =
    CALCULATE (
        SUM ( Database[Volume in UC] ),
        FILTER ( Database, Database[Version] = "ACT" && Database[xxx] = "SS" )
    )
VAR ACT_2 =
    CALCULATE ( SUM ( Database[Volume in UC] ), Database[Version] = "ACT" )
VAR SHARE_4 =
    DIVIDE ( ACT_1, ACT_2, -1 )
RETURN
    SHARE_3 - SHARE_4

     

     

    Best Regards,
    Dangar

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.