Help on modeling to calculate specific row values and displaying as a 'card' in power BI

[onixlee]

Hi, I'm having trouble using the DAX functions on getting my desired outcome.

I have a piece of excel sheet that has the following data.

1. June's target sales number for SMB

2. June's current sales number for SMB

3. June's target sales number for Enterprise

4. June's current sales number for Enterprise

5. Accumulated target sales number (up to June) for SMB

6. Accumulated actual sales number (up to June) for SMB

7. Accumulated target sales number (up to June) for Enterprise

8. Accumulated actual sales number (up to June) for Enterprise

9. Total target sales number (for this year) for SMB

10. Total target sales number (for this year) for Enterprise

11. Total target sales number (for this year)

12. Total actual sales number (for thi syear)

My first desired outcome is as following

1. Bar chart displaying #2 vs. #1

2. Bar chart displaying #4 vs. #3

3. Bar chart displaying #6 vs. #5

4. Bar chart displaying #8 vs. #7

5. Bar chart displaying #9 vs. #6

6. Bar chart displaying #10 vs. #7

7. Bar chart displaying #12 vs. #11

(The bar charts don't have to be separated but I would like the charts to display my desired outcome)

My second desired outcome is as following

1. Card displaying percentage attain rate for #1 bar chart

2. Card displaying percentage attain rate for #2 bar chart

3. Card displaying percentage attain rate for #3 bar chart

4. Card displaying percentage attain rate for #4 bar chart

5. Card displaying percentage attain rate for #5 bar chart

6. Card displaying percentage attain rate for #6 bar chart

7. Card displaying percentage attain rate for #7 bar chart

I know the logic may be quite complicated but the ultimate purpose is to provide a dashboard for sales to visualize the current sales status. I am open to all problem solving techniques to solve the above problem.

Excel sheet download path: https://bit.ly/2KdFQl6

Thank you

[v-shex-msft]

Hi @onixlee,

You can try to use below formula to get the percent between actual and target.

Act Vs Tar = 
CALCULATE (
    SUM ( Table1[Amount] ),
    VALUES ( Table1[Month] ),
    VALUES ( Table1[Territory] ),
    Table1[Category] = "Actual"
)
    / CALCULATE (
        SUM ( Table1[Amount] ),
        VALUES ( Table1[Month] ),
        VALUES ( Table1[Territory] ),
        Table1[Category] = "Target"
    )
+0

Regards,

Xiaoxin Sheng

[onixlee]

Hello Xiaoxin,

Thanks for your reply. Since I am not familiar with Power BI, it's difficult for me to input the formula.

I am sorry but can you please attach the pbix file for the below screenshot?

Thank you

Han

[v-shex-msft]

Hi @onixlee,

I attached sample pbix file below.

Regards,

Xiaoxin Sheng