Forum Discussion

dejanzoric's avatar
dejanzoric
Frequent Visitor
2 years ago
Solved

Ratio calculation from same column

The below screenshot is an example of sorted data where we have ID (LN_TAG) and value. I don't know how to make a percentage. The percentage represents the relationship: 01_Real estate/04_Tot...
General Comment
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    according to DAXFormatter.com that code is ok

     

    M =
IF (
    HASONEVALUE ( 'Sheet1'[CATEGORY] ),
    DIVIDE (
        SUM ( 'Sheet1'[SUM_VECA_S4] ),
        CALCULATE (
            SUM ( 'Sheet1'[SUM_VECA_S4] ),
            'Sheet1'[CATEGORY] = "04_Total without duplicating value"
        ),
        0
    )
)

    do you still get the error message?

lbendlin's avatar
lbendlin
Super User
2 years ago

Be aware that in standard scenarios you can add the sum again and then

change the "show value as" to % of Grand Total

Which gives

 

In your scenario you need a measure to say

 

Measure = divide(sum('Table (3)'[SUM]),CALCULATE(sum('Table (3)'[SUM]),'Table (3)'[CATEGORY]="04_Total without duplicating value"),0)

 

 

 

Probably want to suppress the Total value:

 

Measure = if(hasonevalue('Table (3)'[CATEGORY]),divide(sum('Table (3)'[SUM]),CALCULATE(sum('Table (3)'[SUM]),'Table (3)'[CATEGORY]="04_Total without duplicating value"),0))

 

 

 

 

dejanzoric's avatar
dejanzoric
Frequent Visitor
2 years ago

Thanks, lbendlin In fact, I want to achieve it as shown in the last table.

I entered the formula as given in the example but it returns the following error:

"Too many arguments were passed to the SUM function. The maximum argument count for the function is 1".

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    please show your measure code

    • dejanzoric's avatar
      dejanzoric
      Frequent Visitor
      2 years ago

       

      deliver measure code

       

      Measure = if(hasonevalue('Sheet1'[CATEGORY]),
      divide(sum('Sheet1'[SUM_VECA_S4]),
      CALCULATE(sum('Sheet1'[SUM_VECA_S4]),'Sheet1'[CATEGORY]="04_Total without duplicating value"),0))
      • Dangar332's avatar
        Dangar332
        Resident Rockstar
        2 years ago

        hi, dejanzoric 

        try make measure

        % = SUM('Table'[sum])/CALCULATE(SUM('Table'[sum]),'Table'[category]="04_total without duplicating value")

         


         

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