cancel
Showing results forย
Did you mean:ย
Helper IV

## Percent of Goal

Hello community,
I need your great help to create a card that must contain the following values:

It must show the percentage that I have with respect to the goal that is in the second image:

I clarify that the value of the second column corresponds to the current month (first column).

Thanks.

1 ACCEPTED SOLUTION
Microsoft

@jracer007๏ผ

The first two steps are same as my first reply, change step3 and step4, ignore step5 and step6.

3. Create the following columns in the first table

Year = YEAR(Table1[Date])

Month= MONTH(Table1[Date])

max date of nonblank = CALCULATE(MAX(Table1[Date]),FILTER(Table1,Table1[Year]=EARLIER(Table1[Year])&& Table1[Month]=EARLIER(Table1[Month])),NOT(ISBLANK(Table1[Active user])))

last value per month = CALCULATE(FIRSTNONBLANK(Table1[Active user],Table1[Active user]),FILTER(Table1,Table1[Date]=Table1[max date of nonblank] && Table1[Year]=EARLIER(Table1[Year])&& Table1[Month]=EARLIER(Table1[Month])))

percentage = DIVIDE(Table1[last value per month],RELATED(Table2[MITIGOAPP v1]))

4. Create a table visual as below.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Microsoft

@jracer007,

1. In Power BI Desktop, create date table following the guide in this blog.

2. Create relationship between the first table and date table using date field, create relationship between the second table and date table using date field.

3. Create measure in the first table.
sum1 = SUM('Table 1'[Value])

4. Create measure in the second table.
sum2 = SUM('Table 2'[MITIGOAPP v1])

5. Create measure in the first table.
percentage = [sum1]/[sum2]

6. Create a  table visual using YearMonth field of date table and the percentage measure.

If the above steps donโt help, please elaborate your logic and post expected result in table format.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IV

The current value (column K) is equivalent to the current day and the goal (MITIGOAPP v1) is equivalent to the current month.
here tines the two files:

Microsoft

@jracer007๏ผ

What is your expected result ? You can take Dec 2017 for example. And I see some text values in the second column in your first file, do you share correct data in the first file๏ผ

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IV

I'll give you an example,
The target value must be equal to the one in the current month (calendar). then it should be 201.515, and the current value should be from the last day. Example if we are on 12/12/2017 the value should be 113.240

Finally, the missing percentage must be displayed to reach that objective value.

Microsoft

@jracer007๏ผ

The first two steps are same as my first reply, change step3 and step4, ignore step5 and step6.

3. Create the following columns in the first table

Year = YEAR(Table1[Date])

Month= MONTH(Table1[Date])

max date of nonblank = CALCULATE(MAX(Table1[Date]),FILTER(Table1,Table1[Year]=EARLIER(Table1[Year])&& Table1[Month]=EARLIER(Table1[Month])),NOT(ISBLANK(Table1[Active user])))

last value per month = CALCULATE(FIRSTNONBLANK(Table1[Active user],Table1[Active user]),FILTER(Table1,Table1[Date]=Table1[max date of nonblank] && Table1[Year]=EARLIER(Table1[Year])&& Table1[Month]=EARLIER(Table1[Month])))

percentage = DIVIDE(Table1[last value per month],RELATED(Table2[MITIGOAPP v1]))

4. Create a table visual as below.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IV

Great!!

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!