Forum Discussion

julpol's avatar
julpol
Regular Visitor
1 year ago
Solved

Tabular Editor - create multiple calculation items

Hi Guys, i'm trying to understand if there is way to use the power of calculation items (tabular editor) to achieve the following result:   i have a table "Table1" with columns : state, date, amoun...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from AntrikshSharma , please allow me to provide another insight:

    Hi, julpol 

    Regarding the issue you raised, my solution is as follows:

     

    1.I offer two solutions, and you can choose based on your needs:

    One is to use measures:

     

    % of Total1 = 
VAR denominator=CALCULATE(SUM(Table1[CY Rolling 12M]),ALL('Table1'))
VAR Numerator=SUM('Table1'[CY Rolling 12M])
RETURN Numerator/denominator
    % of Total 2 = 
VAR denominator=CALCULATE(SUM(Table1[CY Rolling 12M]),ALL('Table1'))

RETURN 'Table1'[CY Rolling 12M]/denominator

     

    2.Here is the final result, which I hope meets your expectations: 

     

    Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

     

    Please find the attached pbix relevant to the case.

     

    Best Regards,

    Leroy Lu

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

     

AntrikshSharma's avatar
AntrikshSharma
Community Champion
1 year ago

julpol % of total based on [Amount Measure] or [PY Rolling 12M]/[CY Rolling 12M] ?

julpol's avatar
julpol
Regular Visitor
1 year ago

Hi AntrikshSharma 

the formula is :

 

% of Total = [CY Rolling 12M] / Total of [CY Rolling 12M]

 

where [CY Rolling 12M] is calc item an currently the last column

 

Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from AntrikshSharma , please allow me to provide another insight:

    Hi, julpol 

    Regarding the issue you raised, my solution is as follows:

     

    1.I offer two solutions, and you can choose based on your needs:

    One is to use measures:

     

    % of Total1 = 
VAR denominator=CALCULATE(SUM(Table1[CY Rolling 12M]),ALL('Table1'))
VAR Numerator=SUM('Table1'[CY Rolling 12M])
RETURN Numerator/denominator
    % of Total 2 = 
VAR denominator=CALCULATE(SUM(Table1[CY Rolling 12M]),ALL('Table1'))

RETURN 'Table1'[CY Rolling 12M]/denominator

     

    2.Here is the final result, which I hope meets your expectations: 

     

    Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

     

    Please find the attached pbix relevant to the case.

     

    Best Regards,

    Leroy Lu

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