Table with predefined form with multiple measures in columns

[petar_kisdobran]

Hi everyone,

Can I kindly ask you to help me with following challenge, i have two tables, one with actual data and other with budgeted.

I appended them to have data in one query, and tried with measures (calculate function) to get needed outcome (snip below), however cannot make it in the form required (managed to get only actuals or budgeted amounts in required form, cannot add them both in one table)...

table actuals                                          table budgeted

            

Need to have

Measures used: revenue act = calculate(sum('actuals'[Amount act]),'actuals'[Line]="Revenue")

same for CoS and other expenses.

Margin act = [revenue act]-[CoS act]

same made for budgeted amounts, however could not present them side by side in table....

Thank you in advance!

My best,

Petar

[Ahmedx]

You can add any character and apply conditional formatting to its font color. for example make it transparent

CF = IF(SELECTEDVALUE(DimLine[Line])=UNICHAR(152),"#3E4AFF00")

[petar_kisdobran]

sorry for torturing this much, if I would like to add transparency format to your code line unichar (bottom of the actuals measure code)

....

  "", "",

    UNICHAR ( 160 ), "",

    FORMAT ([Act],"#")

)

and to change it for instance to Unichar (130),"", is it possible to add an transparency format to that line ?

eg

....

format (unichar130),#3E4AFF00",

FORMAT ([Act],"#")

)

Thank you so much for all!

My best,

Petar

[Ahmedx]

Hi ! explain how to count the following measures
Margin%
EBITDA
EBITDA%

[petar_kisdobran]

apologies,

margin % = margin / revenue

EBITDA = margin - other expenses

EBITDA % = EBITDA / revenue

BR

Petar

[Ahmedx]

[petar_kisdobran]

thats great 🙂 can you please share the solution (ege PBI file or codes)?

[Ahmedx · Accepted solution]

see attached
https://dropmefiles.com/9cgLD

[petar_kisdobran]

thanks! 
Could you please help me understand how have you managed to enter a space character in DimLine data (line # 7) and that PBI accept this as unique value compared to a blank row (line # 5)? If i try to add another 2x space character (eg line #10), PBI recognised it as duplicated value so I cannot do one->many relationship and your other solutions... 

[Ahmedx]

this is character 160

https://dropmefiles.com/vdZfi

[petar_kisdobran]

awesome! is it possible to add additional ones (eg to make order 10) or this is all we can use?