Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Percent usage in matrix type table in Power BI.

I am in doubt as to how to perform percentages when I go to the next level of drill down in my matrix, the first line was ok, but when I go down the level, 100% is displayed in all data.

 

My base formula is: 

Total Sales Underline = CALCULATE([Sale Qty Weight KG], ALLSELECTED('Item'[Underline - Name]))
% Share Underlines = DIVIDE([Sales Qty Weight KG], [Total Sales Underlines])
 

but there are other levels that need the percentage. Can someone help me?

 

how I use the other lines, like:


Item [Group - Name]
Item - Description [Description]

 

 

@Dax
@divide
@microsoft
@powerbi
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Anonymous 

     

    try like:

    % Share Underlines = 
DIVIDE(
    [Sales Qty Weight KG], 
    CALCULATE(
        [Sale Qty Weight KG], 
        ALLSELECTED('Item'[Description])
)

     

    Tried to simulate your case. Supposing your table looks like:

    then try to plot the matrix with a measure like:

    %Share = 
DIVIDE(
    SUM(TableName[Qty]), 
    CALCULATE(
        SUM(TableName[Qty]), 
        ALLSELECTED(TableName[Description])
    )
)

    it worked like:

     

     

     

3 Replies

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

    hi Anonymous 

     

    try like:

    % Share Underlines = 
DIVIDE(
    [Sales Qty Weight KG], 
    CALCULATE(
        [Sale Qty Weight KG], 
        ALLSELECTED('Item'[Description])
)

     

    Tried to simulate your case. Supposing your table looks like:

    then try to plot the matrix with a measure like:

    %Share = 
DIVIDE(
    SUM(TableName[Qty]), 
    CALCULATE(
        SUM(TableName[Qty]), 
        ALLSELECTED(TableName[Description])
    )
)

    it worked like:

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Good morning,

       

      It worked, thank you very much!

       

      In this case, I used the formula as:

      % Participation Underlines = DIVIDE(
    [Sale Qty Weight KG],
     CALCULATE([Sale Qty Weight KG],
     ALLSELECTED('Item - Description'[Item - Description]), ALLSELECTED('Item'[Group - Name]), ALLSELECTED('Item'[Underline - Name])
))

       

       

  • andhiii079845's avatar
    andhiii079845
    Icon for Solution Sage rankSolution Sage
    3 years ago

    What happended when you add Item [Group - Name]
    Item - Description [Description]  in the this statement? Like;

    ALLSELECTED('Item'[Underline - Name],Item [Group - Name])