Scenario based on some requirement - unable to achive in power bi - any ideas ?

[ashmitp869]

Hi,

I have a scenraio where I need to calculate a expression in Power BI. I know the calculation of Gross Margin Excluding Overheads % but I don't know how to achieve in dax.

Qlikview report

Power BI my report :

Let me know if anyone give their ideas.

Thanks

[v-jianpeng-msft · Accepted solution]

Hi, @ashmitp869 

Thanks for the reply from @Uzi2019 , please allow me to provide another insight. Based on your description, I created part of the data, and the following is a reference:

I used the following DAX expression:

Budget Total Gross Margin % =
VAR _part1 =
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Level1] = "DIRECT COSTS" ),
        'Table'[Budget Total Gross]
    )
VAR _part2 =
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Level1] = "REVENUE" ),
        'Table'[Budget Total Gross]
    )
RETURN
    DIVIDE ( _part2 - _part1, _part2 )

Here are the results:

I've read the replies between Uzi2019 and you, you can try to find the result of each part in the card visual first, and then do a sum. If you haven't solved the problem yet, can you provide me with a copy of the sample data that does not contain privacy?

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[ashmitp869]

Hi Jianpeng Li,

Thanks for the dax calculation. It helped me to achieve the result.

One the requirement is to only show at the total , but in my case is calcualting row by row.

Is it possible to achieve like below:

One more help needed , How did you achieve this layout in power bi

I am using Matrix visual but the visual is like this.

[v-jianpeng-msft]

Hi, @ashmitp869 

Thank you very much for your reply. I guess you can try the ISINSCOPE or HASONEVALUE function to tell if the current row is a total row, if not, then you set it to blank() or -, if yes then do your calculation. Here's a reference:

I created a measure and used the above function:

This function returns true or false. Therefore, we can use the if function to perform the calculation when it returns false, otherwise it is -. As shown in the figure below:

If you want to implement my layout above, you can turn it off by following the image below, and you'll see the layout I replied to for the first time:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[ashmitp869]

Thanks a lot, you answered all my questions.

[Uzi2019]

Hi @ashmitp869 
can you put all related data in table and explain which value should be used for calculation like ??
(A- B)/C * 100

As per you data Revenue is not a seperate column it is value of a column service group.  correct??

[ashmitp869]

You are correct "REVENUE" is the value coming from Service Group Level 0.Please see the data model attached.

The Extended Amount needs to be consider.

Calculation of Extended Amount is 

CALCULATE(SUM('vFactBudgetActuals'[Extended Amount]))

[ashmitp869]

I tried the below dax but didn't acheive my the result

Budget Total Gross Margin % =

    CALCULATE([Extended Amount],FILTER(SM,SM[Service Group Level 0] ="REVENUE"),ALL('Date'[Year],'Date'[Month Name])) -

    CALCULATE([Extended Amount],FILTER(SM,SM[Service Group Level 1] ="DIRECT COSTS"),ALL('Date'[Year],'Date'[Month Name]))/

    CALCULATE(SUM('vFactBudgetActuals'[Extended Amount]),FILTER(SM,SM[Service Group Level 0] ="REVENUE"),ALL('Date'[Year],'Date'[Month Name]))