Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
memons
Frequent Visitor

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

 

MonthDistrictTehsilUCDayTargetCoverage% Coverage
Jan 24ABC115000250017%
Jan 24ABC215000280019%
Jan 24ABC315000300020%
Jan 24ABC415000350023%
Jan 24ABC515000170011%
Jan 24XYZ110000200020%
Jan 24XYZ210000250025%
Jan 24XYZ310000250025%
Jan 24XYZ410000200020%
Feb 24ABC115000150010%
Feb 24ABC215000150010%
Feb 24ABC315000300020%
Feb 24XYZ110000600060%
Feb 24XYZ210000100010%
Feb 24XYZ3100005005%
Feb 24XYZ410000150015%

 

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.

1 ACCEPTED SOLUTION
Irwan
Memorable Member
Memorable Member

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.

Irwan_3-1725849652931.png

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

Irwan_2-1725849630929.png

 

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.

View solution in original post

13 REPLIES 13
Irwan
Memorable Member
Memorable Member

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.

Irwan_3-1725849652931.png

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

Irwan_2-1725849630929.png

 

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
Frequent Visitor

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
Memorable Member
Memorable Member

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
Frequent Visitor

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
Memorable Member
Memorable Member

@memons 

 

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

Thank you.

memons
Frequent Visitor

memons_0-1725868637781.png

 

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

memons
Frequent Visitor

memons_1-1725870319582.png

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

 

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

Dear Thanks let me check and get back to you

Irwan
Memorable Member
Memorable Member

@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
Frequent Visitor

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

lbendlin
Super User
Super User

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.

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.