Calculate total percentage overall for each da, but with constant repetitive target against each day

[memons]

Month	District	Tehsil	UC	Day	Target	Coverage	% Coverage
Jan 24	A	B	C	1	15000	2500	17%
Jan 24	A	B	C	2	15000	2800	19%
Jan 24	A	B	C	3	15000	3000	20%
Jan 24	A	B	C	4	15000	3500	23%
Jan 24	A	B	C	5	15000	1700	11%
Jan 24	X	Y	Z	1	10000	2000	20%
Jan 24	X	Y	Z	2	10000	2500	25%
Jan 24	X	Y	Z	3	10000	2500	25%
Jan 24	X	Y	Z	4	10000	2000	20%
Feb 24	A	B	C	1	15000	1500	10%
Feb 24	A	B	C	2	15000	1500	10%
Feb 24	A	B	C	3	15000	3000	20%
Feb 24	X	Y	Z	1	10000	6000	60%
Feb 24	X	Y	Z	2	10000	1000	10%
Feb 24	X	Y	Z	3	10000	500	5%
Feb 24	X	Y	Z	4	10000	1500	15%

I want to create a chart in Power BI with Target & Coverage in bar Chart & % in line chart. I already have created above chart.

I am having issue of making chart where target is considered once and coverages are considered cumulative and when any single day is selected % should be based on that Target which was selected once & Coverage corresponding to that Specific day and when all days are selected, then the target should be considered once, and coverage should be cumulative.

Is there any way to do this without changing any of the data. it is doable via Measure or Dax Formula

Please note that i am Rookie in PowerBI.

Thanking in advance.

[Irwan · Accepted solution]

hello @memons 

please check if this accomodate your need. I assumed you have %Coverage in your data.

before day selection, maximum target divided by cumulative coverage.

after day selection, respectively coverage divided by its target value (which is also shown in your %Coverage).

since you dont want to modify the data, then you need to do this by measure.

create a new measure with following DAX.

Calculation = 
var _Target = SELECTEDVALUE('Table'[Target])
var _Coverage = SELECTEDVALUE('Table'[Coverage])
var _Month = SELECTEDVALUE('Table'[Month])
var _Day = SELECTEDVALUE('Table'[Day])
var _District = SELECTEDVALUE('Table'[District])
var _Cumulative = 
SUMX(
    FILTER(
        ALLSELECTED('Table'),
        'Table'[District]=_District&&
        'Table'[Month]=_Month&&
        'Table'[Day]<=_Day
    ),
    'Table'[Coverage]
)
Return
IF(
    not (HASONEVALUE('Table'[Month])&&HASONEVALUE('Table'[Day])),
    DIVIDE(_Coverage,_Target),
    DIVIDE(_Cumulative,MAX('Table'[Target]))
)

since you dont really explain how the cumulative coverage works, I assumed it is based on month and district value (same month and same district will be cumulatively sum for each of its day).

however, as @lbendlin , it is better to have separated date table (your table has day value separated from month-year, I guessed it has itsown purpose).

Hope this will help.

Thank you.

[memons]

I have date table as well in form of timestamp column, but sometime same data is entered on the other day in database file but day is kept as previous, so if i sort the data by date it would not satisfy me need of daily coverage update.

for example if day 1 is selected, then coverage should be 3000/15000, and if all five days selected then coverage should be sum of all five days coverage divided total Target which should be equal to 15000 not 75000. 13500/15000, not 13500/75000. Now i hope you understand my question at hand here.

[Irwan]

hello @memons 

i believe i tried to make the DAX works like you need.

you can see from the screenshot, when the day is chosen, the value is only calculated for that day.

and when no selection is made, the value is calculated based on cumulative coverage and maximum target (not cumulative target).

Does it not work in your original data?

Thank you.

[memons]

Dear I am currently stuck as i have created measures for calculation of certain field columns which are required to add up value for summation of Admin coverages like Coverage in House, and coverage out of house, now sum of both is then divided by target to get % total coverages.

Now in my table dax measures are not visible to add. How can i include another dax measure in creation of current dax as suggested by you

[Irwan]

@memons 

It would be great if there are screenshots of "not visible to add"

Thank you.

[memons]

above screenshot is shown applied parameters and errors, kindly provide assistance here

[memons]

I have shown % Admin Coverages already created as a measure, but it is still showing error

[gmsamborn]

Hi @memons 

For the second line of your measure, try this:
VAR _Coverage = [% Admin Coverage]

SELECTEDVALUE works with columns, not measures.

Also, when referring to a measure, as a best practice, don't include the table name. Conversely, always include the table name when referring to a column. This makes it easy to differentiate.  (Insider of iterators, column names in the table being iterated don't need table names.)

[memons]

Dear Thanks let me check and get back to you

[Irwan]

@memons 

Error at line 2, not sure but looks like you dont have column named % Admin Coverage (but you do have Admin Coverage as there is no error in line 14).

Error at line 11, you didnt put _D in table name

Error at line 19, you havent defined _AdminCoverage variable and use _Target in Final_HH_Target as you have define Final_HH_Target in variable _Target.

Thank you.

[memons]

Thanks for response, let me check and confirm whether this works.

[lbendlin]

For the target you can use a "Constant Line - Y axis"  feature.

Please include a date column, and a Calendar table, in your data model.

[memons]

I already have date column in my actual data along with day column, but i need day wise calculation for each day and for final total i need the target to be considered single time

Like i want coverage for Feb day 1, it should show 3000/15000 similarly if i want to see all five day coverage, then again 12000/15000. This Target would remain same and should not sum all the targets