Forum Discussion

pg1980's avatar
pg1980
Icon for Helper II rankHelper II
3 years ago
Solved

Percentage Square Matrix (with Total row)

Hi ! I have to be able to make a analysis in a matrix visual. In this analysis I have to sum up the quantity values by “year-month New” and go substracting this number to the total quantity of each ...
  • OwenAuger's avatar
    OwenAuger
    3 years ago

    Hi pg1980 

    See attached PBIX.

    I haven't looked closely at other responses so may be repeating things 🙂

     

    Model:

    Month Index is the number of months between New & Out plus 1.

    Key measures:

    Total Quantity = 
SUM ( 'YourTable'[Quantity] )

Quantity Out Cumulative by Index = 
VAR MaxIndex =
    MAX ( 'Month Index'[Month Index] )
RETURN
    CALCULATE (
        [Total Quantity],
        'Month Index'[Month Index] <= MaxIndex,
        NOT ISBLANK ( 'Month Index'[Month Index] ),
        REMOVEFILTERS ( 'Month Index' )
    )

Total Quantity All Index = 
CALCULATE (
    [Total Quantity],
    REMOVEFILTERS ( 'Month Index' )
)

Net Quantity Cumulative by Index = 
[Total Quantity All Index] - [Quantity Out Cumulative by Index]

Net Quantity Cumulative by Index Previous = 
VAR MaxIndex =
    MAX ( 'Month Index'[Month Index] )
RETURN
    CALCULATE (
        [Net Quantity Cumulative by Index],
        'Month Index'[Month Index] = MaxIndex - 1,
        REMOVEFILTERS ( 'Month Index'[Month Index])
    )

Percentage = 
DIVIDE (
    [Net Quantity Cumulative by Index],
    [Net Quantity Cumulative by Index Previous]
)

     

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
3 years ago

Hi pg1980 

See attached PBIX.

I haven't looked closely at other responses so may be repeating things 🙂

 

Model:

Month Index is the number of months between New & Out plus 1.

Key measures:

Total Quantity = 
SUM ( 'YourTable'[Quantity] )

Quantity Out Cumulative by Index = 
VAR MaxIndex =
    MAX ( 'Month Index'[Month Index] )
RETURN
    CALCULATE (
        [Total Quantity],
        'Month Index'[Month Index] <= MaxIndex,
        NOT ISBLANK ( 'Month Index'[Month Index] ),
        REMOVEFILTERS ( 'Month Index' )
    )

Total Quantity All Index = 
CALCULATE (
    [Total Quantity],
    REMOVEFILTERS ( 'Month Index' )
)

Net Quantity Cumulative by Index = 
[Total Quantity All Index] - [Quantity Out Cumulative by Index]

Net Quantity Cumulative by Index Previous = 
VAR MaxIndex =
    MAX ( 'Month Index'[Month Index] )
RETURN
    CALCULATE (
        [Net Quantity Cumulative by Index],
        'Month Index'[Month Index] = MaxIndex - 1,
        REMOVEFILTERS ( 'Month Index'[Month Index])
    )

Percentage = 
DIVIDE (
    [Net Quantity Cumulative by Index],
    [Net Quantity Cumulative by Index Previous]
)