Ratio calculation from same column

[dejanzoric]

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_Total without duplicating values
02_Insurance policy/04_Total without duplicating values
03_Real estate valuation/04_Total without duplicating values
04_Total without duplicating values/04_Total without duplicating values

I am unable to do so in Power BI, please help.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[dejanzoric]

Hi, first of all thank you for wanting to help me.

I am attaching a data table

CATEGORY	SUM	%
01_Real estate	19.010.195
02_Insurance policy	40.228.138
03_Real estate valuation	6.820.222
04_Total without duplicating value	59.174.339

I want to calculate the percentage of participation in the total in the table

The percentage represents the relationship:
01_Real estate/04_Total without duplicating values
02_Insurance policy/04_Total without duplicating values
03_Real estate valuation/04_Total without duplicating values
04_Total without duplicating values/04_Total without duplicating values

Thanks in advance,

Dejan

[Dangar332]

hi, @dejanzoric 

try these one 

% =

var num = SUM('Table'[SUM])

var deno = CALCULATE(SUM('Table'[SUM]),'Table'[CATEGORY]="04_Total without duplicating value ")

return

DIVIDE(num,deno)

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

[Ashish_Mathur]

Hi,

Share the raw data, not the visual.

[lbendlin]

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]

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]

please show your measure code

[dejanzoric]

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))

[lbendlin · Accepted solution]

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?

[dejanzoric]

Hi, the formula is OK.

I only tried to import the table and run the formula from it and it works.

Obviously, the problem is when I want to run a formula from the model where the union tables are derived. Then a message appears.

I will try to do the data transformation in a different way first and I think it will work then. Thank you very much for your help.

[Dangar332]

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.