Percentage visualization

[Cherry04]

Hi all,

Please help to advice my below case. thank you!

I have a measure: 

varTarvsAct = DIVIDE(SUM('Act'[Actual Cost]),SUM('Bud'[Target Cost]))-1

in which: Actual cost data from Jan to Apr
TArget cost have data from Jan to Dec

Actual all is higher than Target cost

If I choose Jan, the Cards is all correct, the clustered column chart show correct for Jan but in negative %, i think must be >0%, and Feb-Apr: % is all bellow to 0%

The same if I choose Feb, and Jan is under 0% also

Please advice how can I present correct for the un-choosen month. Thank all for your help!!

[v-venuppu · Accepted solution]

Hi @Cherry04 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Ashish_Excel for the prompt response.

I have created pbix file using sample data.Here are some steps followed to generate pbix:

1.Loaded sample data into Powerbi.

2.Created Relationships, you can check in attached pbix file.

3.Created DAX measures (Actualcost,Targetcost,%Diff).

4.Sorted MonthName by MonthNum to maintain chronological order.

You can go through the attached pbix file for your reference.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[v-venuppu]

Hi @Cherry04 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @Cherry04 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @Cherry04 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Ashish_Excel @rohit1991 for the prompt response.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[rohit1991 · Accepted solution]

Hi @Cherry04 ,
From your screenshots and DAX measure, it seems the issue lies in how the calculation behaves when specific months are selected. Your current measure:

varTarvsAct = DIVIDE(SUM('Act'[Actual Cost]), SUM('Bud'[Target Cost])) - 1

is context-sensitive. That means, when you filter by a specific month (e.g., February), the SUM('Act'[Actual Cost]) and SUM('Bud'[Target Cost]) only calculate for that selected month, and months that are not selected show as blank or misrepresented (often as 0%), which may explain the negative values you're seeing.

To fix this and always compare each month’s actual vs target independently, regardless of slicer selections, you can modify your DAX like this:

varTarvsAct = 
DIVIDE(
    CALCULATE(SUM('Act'[Actual Cost]), ALLSELECTED('Date'[Month])),
    CALCULATE(SUM('Bud'[Target Cost]), ALLSELECTED('Date'[Month]))
) - 1

Or, if you're plotting data by month and need values for all months, you can try removing the month filter context from one side:

varTarvsAct = 
DIVIDE(
    SUM('Act'[Actual Cost]),
    CALCULATE(SUM('Bud'[Target Cost]), ALLEXCEPT('Bud', 'Bud'[Month]))
) - 1

This will ensure that the line or column charts show accurate variance percentages for each month, even when only one month is selected in a slicer. Alternatively, you can build a disconnected table for month selection and use it only for slicer purposes while keeping calculations independent.

[v-venuppu · Accepted solution]

Hi @Cherry04 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Ashish_Excel for the prompt response.

I have created pbix file using sample data.Here are some steps followed to generate pbix:

1.Loaded sample data into Powerbi.

2.Created Relationships, you can check in attached pbix file.

3.Created DAX measures (Actualcost,Targetcost,%Diff).

4.Sorted MonthName by MonthNum to maintain chronological order.

You can go through the attached pbix file for your reference.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Ashish_Excel]

Hi,

Share the download link of the PBI file and show the problem there clearly.