Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filter function for calculating % of total

Dear All,

 

I need to create a DAX measure for calculating % of a group of customers compared to the total.

That's the mathematical logic: (OB-EX + OB-SH)/(OB-EX + OB-SH+ #)

 

Part.Type05CustomerTurnover
OB-EXA72891
OB-EXB182818
OB-SHC172881
OB-SHD 887
#E7533
#F2498

 

I used the function FILTER, but even with one variable it's not working.

 

 

How should i structure my DAX formula to get the result that i need, please?

 

Thanks a lot!

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    Try this

    Revenue = sum('OB_Local_Currency'[Turnover])

    Measure1 = calculate([Revenue],'OB_Local_Currency'[Part.Type05]="OB-EX"||'OB_Local_Currency'[Part.Type05]="OB-SH")

    Measure2 = DIVIDE([Measure1],[Revenue])

    Drag Measure2 to a card visual.

    Hope this helps.

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    4 years ago

    Anonymous 

    It's going to look something like this.

    %OB LC Customers =
VAR _Numerator =
    CALCULATE (
        SUM ( 'OB_Local Currency'[Turnover] ),
        ALL ( 'OB_Local Currency' ),
        'OB_Local Currency'[PartType05 - SP S (M)] IN { "OB-EX", "OB-SH" }
    )
VAR _Denomenator =
    CALCULATE ( SUM ( 'OB_Local Currency'[Turnover] ), ALL ( 'OB_Local Currency' ) )
RETURN
    DIVIDE ( _Numerator, _Denomenator )
  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi， Anonymous 

    You can try the following DAX expressions.

     

     

    Measure = 
CALCULATE (
    SUM ( 'Table'[Turnover] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Part.Type05] = "OB-EX"
            || 'Table'[Part.Type05] = "OB-SH"
    )
)
    / CALCULATE (
        SUM ( 'Table'[Turnover] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Part.Type05] = "OB-EX"
                || 'Table'[Part.Type05] = "OB-SH"
                || 'Table'[Part.Type05] = "#"
        )
)

     

     

    Best Regards,

    Community Support Team _Charlotte

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

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Try this

    Revenue = sum('OB_Local_Currency'[Turnover])

    Measure1 = calculate([Revenue],'OB_Local_Currency'[Part.Type05]="OB-EX"||'OB_Local_Currency'[Part.Type05]="OB-SH")

    Measure2 = DIVIDE([Measure1],[Revenue])

    Drag Measure2 to a card visual.

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks to you and all solutions! 

    One of them worked for me with some small adaptations. I accepted this one as solution.