topic Re: Get a percentage based on a daily goal in DAX Commands and Tips

Get a percentage based on a daily goal

rconway — Tue, 17 Jan 2023 20:03:59 GMT

I have two simple worksheets in Excel that I am loading into Power BI Desktop. The first worksheet is an aggregated table aggregated encounters per user per day:

The second is a goals sheet that each user needs to meet per day:

I have connected the two "tables" in the Desktop based on the User.

Trying to get a percentage value if a user had any encounters on a given day. Example would be if the date filtered was 1/3/2023 I need AMGRAUBNER to show 17 in the AcctCounts column and 34% in a percentage column in a table visual. All users with encounters for that day would need to be in the table.

Second day using PowerBI so any help is greatly appreciated.

Re: Get a percentage based on a daily goal

Anonymous — Tue, 17 Jan 2023 22:34:40 GMT

hello rconway
you can find the below pbix file: https://1drv.ms/u/s!Ag9tIyk2ofNRjm9wJRUIzsUQ9GsY?e=Hi0tVu

1) create relationship between dimtable and first table by date
2) create relationship between first table users and goal table users

3) create a measure for sum of count column

Sum = calculate(sum(table1[count]))

4)create a measure for goal of each user

Goal By User = Calculate(max(goaltable[goal]),allexcept(goaltable,goaltable[users]))

5) craete a divide measure to get percentage

Percentage = DIVIDE([Sum],[Goal By User],0)

6) add users to table, and your 3 measures and add date from dimdate to a slicer and there you got it

if i helped you solve this problem, a kudos is appreciated and accept this post as solution

Re: Get a percentage based on a daily goal

rconway — Wed, 18 Jan 2023 17:10:29 GMT

Thank you so much. The majority of what I am wanting works now. I have added a Hierarchical Slicer visual and can now choose by Year/Month/Day. The only thing I would like is to somehow update the Goal_by_User and Percentage column to show correct values above the lowest level (Day).

So, if a User had encounters for multiple days in a month, say 5 days, and the daily goal is 50 then the Goal_by_User column would show 250 instead of 50 and the Percentage column would update to the correct Percentage.

Re: Get a percentage based on a daily goal

Anonymous — Thu, 19 Jan 2023 02:33:27 GMT

I dont know how to do it in dax but if you go to power query, the goals table, you can add 2 columns , 1 is a starting date and the second is today for example

The create another new column that lists with the formula

{Number.From([start date])..Number.From([Today])}

Them press on the new column header arrow and chose expand to new rows

Then change type to date and rename it to date and you can delete start dste column and today column

Now just create a relationship between your dste table date and goal table date and whatever you choose the lebgth of dates it will sum up but make sure to change the formula of goals to sum instead of max