Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to 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]))
)
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]))
)
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.
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.
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
above screenshot is shown applied parameters and errors, kindly provide assistance here
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
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.
Thanks for response, let me check and confirm whether this works.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
111 | |
59 | |
57 |