Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Matrix - Measure on the current column time range

Trying to make a matrix where the Rows is a group of category(A/B/C...) and the columns is a time data (YYYYMM) from my time table. I want the value to be a measure of the division where the denominator is in the range of the columns.

Exemple : Let say I have 3 categories A/B/C on 2019-05 I got respectively 10/20/30 of each (so a total of 60).

So my measure should gives me 16,65/33,33/50. On 2019-05 20/40/100 (160) -> 12,5/25/62,5 ... etc.

 

When I try to do this measure it's seem that I have the cumulative total of all time as a denominator.

I try something like :

Part Canal = 
VAR Inscription = SWITCH(
    TRUE(),
    SUM('Prod Asso Inscrites OnLine'[Inscription Prod]) > SUM('GA Asso Inscrites OnLine'[Associations Inscrites]), SUM('Prod Asso Inscrites OnLine'[Inscription Prod]),
    SUM('Prod Asso Inscrites OnLine'[Inscription Prod]) <= SUM('GA Asso Inscrites OnLine'[Associations Inscrites]), SUM('GA Asso Inscrites OnLine'[Associations Inscrites])
)
RETURN DIVIDE(Inscription, Associations[Associations Inscrites - Début])

 

Need Help

3 Replies

  • Iamnvt's avatar
    Iamnvt
    Continued Contributor
    7 years ago

    hi,

     

    you can try this measure:

    Ratio = 
DIVIDE(SUM(Table1[Value]), CALCULATE(SUM(Table1[Value]), ALL(Table1[Category])))

    here is the PBI file

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank but I don't understand how this code take care of the range date of the matrix column.

      • Iamnvt's avatar
        Iamnvt
        Continued Contributor
        7 years ago

        you want to find the percentage over Rows (Category), not Columns (Date).

        (All) function in the formula will remove all the outer filter to have SUM of all category.

        Then Divide function calculates the ratio.

         

        If this still not what you want, then you can post Excel file with the expected result