CODEWITHSUNDEEP
excelpowerbidaxpowerbi-desktop
SilverJack
SilverJack

Reputation: 47

Percentage difference per category in the same raw, Power BI

I'm working on a report in Power BI, and I faced a problem. I want to do a simple thing (in DAX or in every way possible), that is: create a column in the report that is a percentage difference per category specified. The category that I want to consider is just one, and is specified in the same row named "Element to compare". To clarify the question I put here an example:

Element Total Value Element to compare the total total %diff from element specified
A 334 C 178,3%
B 323 D -73,8%
C 120 A -64,1%
D 1234 A 269,5%

I want to create the column "%DIFF FORM ELEMENT SPECIFIED" but I can't find a simple way to do it, anyone can help me? Thank you very much.

Upvotes: 0

Views: 2349

Answers (2)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

Use DAX to get the value need for finding difference and then do the calcualtion. I'm splitting this into 2 for better understanding.

  1. create a new column using LOOKUPVALUE to get the value to be compared.

    Value to be compared = LOOKUPVALUE([Total Value],[Element],[Elemet to be compared])

    enter image description here

  2. Now it is simple.Use your calculation in the another column. Make sure you are using % format for this column.

    total %diff from element specified = ('Table'[Total Value] - 'Table'[Value to be compared])/'Table'[Value to be compared]

    enter image description here

Upvotes: 0

sergiom
sergiom

Reputation: 4877

Assuming your data to be the table you posted in your question without the % column

T table

T table

it's possible to write a measure to compute the current row total value, then the value for the element to compare, by setting a filter context over the element to compare with CALCULATE and at last compute the percentage using DIVIDE

%DIFF FORM ELEMENT SPECIFIED =
VAR CurrentValue =
    SUM ( T[Total Value] )
VAR ElementToCompare =
    SELECTEDVALUE ( T[Element to compare the total] )
VAR CompareValue =
    CALCULATE (
        SUM ( T[Total Value] ),
        T[Element] = ElementToCompare,
        REMOVEFILTERS ( T )
    )
VAR Result =
    DIVIDE (
        CurrentValue - CompareValue,
        CompareValue
    )
RETURN
    Result

using this measure in a table visual we get the desired table

resulting table visual

Upvotes: 2

Related Questions