Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filter data on specific value to calculate % share

Hello,

I have following kind of value

I need to calculate % share of each sales person against Zone Total. Following is the format of the required representation

Not be able to figure out how to write the dax for the % share. 

 

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Anonymous 

    try to plot a matrix visual with

     

    1) source column

    2) a calculated column like:

    YYMM = FORMAT(DATE([Year], [Month], 1), "mmm-yy")

    3) two measures like:

    Total Sales = SUM(data[TotalSales])
    % of Share of Zone = 
DIVIDE(
    SUM(data[TotalSales]),
    CALCULATE(
        SUM(data[TotalSales]),
        ALL(data[Source])
    )
)*2

    it worked like:

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous 

    try to plot a matrix visual with

     

    1) source column

    2) a calculated column like:

    YYMM = FORMAT(DATE([Year], [Month], 1), "mmm-yy")

    3) two measures like:

    Total Sales = SUM(data[TotalSales])
    % of Share of Zone = 
DIVIDE(
    SUM(data[TotalSales]),
    CALCULATE(
        SUM(data[TotalSales]),
        ALL(data[Source])
    )
)*2

    it worked like:

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you for your reply. Since there will be some filters in this page I may have to change the dax for % share and to include the source column in the ALL filter. Let me try