Calculation based on Rows with specific attribute value.

[sonuojha1]

Dear Folks,

I have a below use case:

I summarized the data based on Type as below from my detailed data.

Now my requirement is to add new calculated rows as per the formula.

CALC1=Revenue + Expense

CALC2=CALC1+DEP

NET CALC=CALC2+INTERNET_EXP

Kindly note, I want to add calculated rows in the same Matrix view or any other view if possible, and the data shown above is summarized.

How can we do this in power bi?

Any help would be appreciated.

Thanks

Sonu

[manikumar34]

Hi, 

Create a table with the column names whic is TYPE in you case. 

Create measures for value 1 &2 and the calumated masures. 

Use

VALUE1 = SWITHC(TRUE(),SELECTEDVALUE(table[Type])="REVENUE", [Value1 Revenue Measure],

SELECTEDVALUE(table[Type])="EXPENSE", [Value1 ExpenseMeasure],

.............................

Create the second VALUE2 measure with the same DAX. 

On Matrix use the column as TYPE from the static table that created and in values place VALUE1 and VALUE2 measures.

Regards, 

Manikumar

[sonuojha1]

Dear Mani,

Thanks for the reply.

How would it get the CALC1, CALC2, NET_CALC as a row value in Column Type, As these are not available in Type column.

CALC1	382985	620240
CALC2	228382	460596
Net Calc	228187	460596

[manikumar34]

Hi,

Create a static table from Home>Create Table

Create measures for EXPENSE Type for VALUE1 and VALUE2 and the same for Revenue,CValc1 and 2.....

In Case VALUE and VALUE2 are from table as columns on Matrix you don't need to calculate two measures for each type.

Then use the belwo measure to Switch the values according to the TYPE

VALUE1 = SWITHC(TRUE(),SELECTEDVALUE(table[Column1])="REVENUE", [Value1 Revenue Measure],

SELECTEDVALUE(table[Type])="EXPENSE", [Value1 ExpenseMeasure],

.............................

and for VALUE2 the same as above. 

Refer the below link to use SWICTH and TRUE

https://radacad.com/write-conditional-statement-using-switch-in-dax-and-power-bi

Regards, 

Manikumar 

[sonuojha1]

Thanks Mani,

It works.

Now, What if I need these values( CALC1, CALC2, NET_CALC, and numeric values ) as a subtotal.

[manikumar34 · Accepted solution]

@sonuojha1 

I'm afraid that I don't have an Idea over here as we don't have an option to choose Particular Values to make Subtotals. 

Please try the same with creating another static table use the columns over here and check if that works. 

If you like my solution, please accept it and leave a like. 

Regards, 

Manikumar