Forum Discussion

venug20's avatar
venug20
Icon for Resolver I rankResolver I
8 years ago
Solved

Sum sales , Category is Less than; 10000 (Using DAX Only)

Hi Every one,   I have table like this   Category Sales a 20000 b 9000 c 8000 d 7000 e 6000 f 5000 g 4000 h 3000   I want to show my table like below (sum...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    HI venug20

     

    Using DAX there could be three ways of doing this

     

    1) Calculated Table

    2) Measure

    3) Calculated Column

     

     

    1) CALCULATED TABLE

    From the Modelling Tab>>New Table

     

    Calculated_Table =
CALCULATETABLE (
    Table1,
    FILTER (
        VALUES ( Table1[Category] ),
        CALCULATE ( SUM ( Table1[Sales] ) < 10000 )
    )
)

    2) MEASURE

     

    Measure =
IF (
    HASONEFILTER ( Table1[Category] ),
    IF ( SUM ( Table1[Sales] ) < 10000, SUM ( Table1[Sales] ) ),
    SUMX (
        VALUES ( Table1[Category] ),
        IF (
            CALCULATE ( SUM ( Table1[Sales] ) ) < 10000,
            CALCULATE ( SUM ( Table1[Sales] ) )
        )
    )
)

    3) CALCULATED COLUMN

     

    Sales < 10000 =
VAR result =
    CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Category] ) )
RETURN
    IF ( result < 10000, result )
rema's avatar
rema
Icon for Advocate I rankAdvocate I
8 years ago

Hi venug20

Do you want to sort the column in descending order?

 

Thanks,

Rema

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    venug20

     

    You can use VISUAL level filters

     

    • venug20's avatar
      venug20
      Icon for Resolver I rankResolver I
      8 years ago

      Hi Zubair,

       

      Thanks for responding, you have given perfect solution for this... 

       

      I want same result using "DAX"...... Can you help on this.....

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        HI venug20

         

        Using DAX there could be three ways of doing this

         

        1) Calculated Table

        2) Measure

        3) Calculated Column

         

         

        1) CALCULATED TABLE

        From the Modelling Tab>>New Table

         

        Calculated_Table =
CALCULATETABLE (
    Table1,
    FILTER (
        VALUES ( Table1[Category] ),
        CALCULATE ( SUM ( Table1[Sales] ) < 10000 )
    )
)

        2) MEASURE

         

        Measure =
IF (
    HASONEFILTER ( Table1[Category] ),
    IF ( SUM ( Table1[Sales] ) < 10000, SUM ( Table1[Sales] ) ),
    SUMX (
        VALUES ( Table1[Category] ),
        IF (
            CALCULATE ( SUM ( Table1[Sales] ) ) < 10000,
            CALCULATE ( SUM ( Table1[Sales] ) )
        )
    )
)

        3) CALCULATED COLUMN

         

        Sales < 10000 =
VAR result =
    CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Category] ) )
RETURN
    IF ( result < 10000, result )