cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

Percent of Goal

Hello community,
I need your great help to create a card that must contain the following values:
Opera Instantánea_2017-12-11_104041_docs.google.com.png


It must show the percentage that I have with respect to the goal that is in the second image:
Opera Instantánea_2017-12-11_104405_docs.google.com.png

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

Thanks.

1 ACCEPTED SOLUTION

@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.
1.JPG



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.

View solution in original post

6 REPLIES 6
Microsoft
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.

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:
https://docs.google.com/spreadsheets/d/1jvF4xmXOE_TdBfGvwsJNWVqFMpUcoht5wGm_9Gf9hJI/edit?usp=sharing
https://docs.google.com/spreadsheets/d/1OnHTSvbXXmg666DO-ey5tA2GqmeJ8ICiRej03E69CwU/edit?usp=sharing

@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.

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.

@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.
1.JPG



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.

View solution in original post

Great!!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors