Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi Everyone
I have this table model as an input to my report:
Domain | Country | Contract | Spend | Report_Date |
Maintenance | US | YES | 351256 | January |
Maintenance | US | NO | 442451 | January |
Maintenance | UK | YES | 354326 | January |
Maintenance | UK | NO | 326885 | January |
Maintenance | GER | YES | 297713 | January |
Maintenance | GER | NO | 490954 | January |
Logistics | US | YES | 360023 | January |
Logistics | US | NO | 397210 | January |
Logistics | UK | YES | 302325 | January |
Logistics | UK | NO | 318854 | January |
Logistics | GER | YES | 315901 | January |
Logistics | GER | NO | 435740 | January |
Maintenance | US | YES | 396521 | February |
Maintenance | US | NO | 469540 | February |
Maintenance | UK | YES | 414971 | February |
Maintenance | UK | NO | 258856 | February |
Maintenance | GER | YES | 314071 | February |
Maintenance | GER | NO | 435208 | February |
Logistics | US | YES | 471843 | February |
Logistics | US | NO | 411614 | February |
Logistics | UK | YES | 376039 | February |
Logistics | UK | NO | 429307 | February |
Logistics | GER | YES | 411857 | February |
Logistics | GER | NO | 253510 | February |
Monthly the table will be updated, the first two columns are filters and I want to create a graphic with the subtotals like I use to do in Excel :
Something like this:
As you may see is a KPI with the development over time, but I only achieve this in Power BI with the matrix visualization, when I try to create the line chart it only takes the % over the total, not the files or columns. Is there any way to do it?
Thank you.
Solved! Go to Solution.
Hi @moleo ,
I glad it worked for you.
First I create a measure to sum all amount values where the contract is YES and another where the contract is NO and the value is dividing by the amount of YES and NO (ALL(Table[Contract]), this removes the context filter on this column and get all the values on your table.
Another version is using ALL(Table[Report_Date]), it's the same idea, it's not considering the context filter for Report_Date.
You can find more functions about context filter in this link:
https://www.infointelligence.com.br/2017/11/13/usando-allexcept-versus-all-e-values/
Please, if you consider it as a solution, mark it and kudos.
Thank you,
Ricardo
Hi @moleo ,
Has @camargos88 solved your problem?
If he has, please accept his reply as a solution so that people who may have the same question can get the solution directly.
If not, please let me know.
Best Regards,
Icey
That was great and fast, Thank you @camargos88 , 😀 Can you explain to me the solution, I need to escalate this to a KPI with 3 stages "Yes" "Almost Yes" and "No"
Hi @moleo ,
I glad it worked for you.
First I create a measure to sum all amount values where the contract is YES and another where the contract is NO and the value is dividing by the amount of YES and NO (ALL(Table[Contract]), this removes the context filter on this column and get all the values on your table.
Another version is using ALL(Table[Report_Date]), it's the same idea, it's not considering the context filter for Report_Date.
You can find more functions about context filter in this link:
https://www.infointelligence.com.br/2017/11/13/usando-allexcept-versus-all-e-values/
Please, if you consider it as a solution, mark it and kudos.
Thank you,
Ricardo
User | Count |
---|---|
75 | |
75 | |
45 | |
31 | |
27 |
User | Count |
---|---|
99 | |
89 | |
52 | |
48 | |
46 |