Forum Discussion
Create Stacked Column Chart using only measures
I have 3 Tables and created measuers in all these tables, using these measures i want to create a stacked column chart.
But, only using measures i cant create it as i need one dimension column.
Below are the tables and measures i created (example).
Sale Stge: Table1
-----------------------
| Country | Sales_Stage | Amount K$ | Month |
| India | 1 | 500 | Jan-19 |
| India | 2 | 400 | Jan-19 |
| India | 3 | 300 | Jan-19 |
| India | 4 | 250 | Jan-19 |
| America | 1 | 800 | Jan-19 |
| America | 2 | 500 | Jan-19 |
| America | 3 | 300 | Jan-19 |
| America | 4 | 150 | Jan-19 |
Measures for sale stge:
------------------
Sales1 = CALCULATE(SUM(Sale Stge[Amount K$]),FILTER(ALL(Sale Stge[Sales_Stage]), Sale Stge[Sales_Stage] >=1 && Sale Stge[Sales_Stage2] <=2)) --- Ans. 2200
Sales2 = CALCULATE(SUM(Sale Stge[Amount K$]),FILTER(ALL(Sale Stge[Sales_Stage]), Sale Stge[Sales_Stage] >=3 && Sale Stge[Sales_Stage2] <=4)) --- 1000
Sales All = SUM(Sale Stge[Amount K$]) --3200
Sales: Table2
------------------------
| Country | Amount K$ | Month |
| India | 200 | Jan-19 |
| America | 100 | Jan-19 |
Measures for Sales:
--------------------------
Sales M= SUM(Sales[Amount K$])
Budget: Table3
------------------------
| Country | Amount K$ | Month |
| India | 600 | Jan-19 |
| America | 850 | Jan-19 |
Measures for Budget:
-----------------------------
Budget = SUM(Budget[Amount K$])
Remaing Budget = Budget - [Sales M]
Using all above measures i want to create a table like beow and then create a stacked column chart over it. Also give country as a slicer.
Table for Stack Column Chart: ( unable to create like below)
-------------------------------------
| Sales info | Sales1 | Sales2 | Sales All | Budget | Sales | Remaing Budget |
| Sales Stage | 2200 | 1000 | 3200 | |||
| Budget | 1450 | 300 | 1150 |
hi, Anonymous
For your requirement (only using measures to create the visual as you want), It is impossible in power bi for now.
But you could just adjust your formula as below:
Step1:
Add a Sales info fact table.
(only using measures can't create it as it need one dimension column.)
Step2:
Adjust your formula
Sales1 = IF(SELECTEDVALUE(info[Sales info])="Sales Stage", CALCULATE(SUM('Sale Stge'[Amount K$]),FILTER(ALL('Sale Stge'[Sales_Stage]), 'Sale Stge'[Sales_Stage] >=1 && 'Sale Stge'[Sales_Stage] <=2)))Sales2 = IF(SELECTEDVALUE(info[Sales info])="Sales Stage",CALCULATE(SUM('Sale Stge'[Amount K$]),FILTER(ALL('Sale Stge'[Sales_Stage]), 'Sale Stge'[Sales_Stage] >=3 && 'Sale Stge'[Sales_Stage] <=4)) )Sales All = IF(SELECTEDVALUE(info[Sales info])="Sales Stage", SUM('Sale Stge'[Amount K$]))Sales All = IF(SELECTEDVALUE(info[Sales info])="Sales Stage", SUM('Sale Stge'[Amount K$]))Budget = IF(SELECTEDVALUE(info[Sales info])="Budget",SUM(Budget[Amount K$]))
Remaing Budget = IF(SELECTEDVALUE(info[Sales info])="Budget", Budget[Budget] - [Sales M])
for Also give country as a slicer.
You could add a country fact table and then create the relationship with these three tables.
Result:
and here is pbix file, please try it..
Best Regards,
Lin
3 Replies
hi, Anonymous
For your requirement (only using measures to create the visual as you want), It is impossible in power bi for now.
But you could just adjust your formula as below:
Step1:
Add a Sales info fact table.
(only using measures can't create it as it need one dimension column.)
Step2:
Adjust your formula
Sales1 = IF(SELECTEDVALUE(info[Sales info])="Sales Stage", CALCULATE(SUM('Sale Stge'[Amount K$]),FILTER(ALL('Sale Stge'[Sales_Stage]), 'Sale Stge'[Sales_Stage] >=1 && 'Sale Stge'[Sales_Stage] <=2)))Sales2 = IF(SELECTEDVALUE(info[Sales info])="Sales Stage",CALCULATE(SUM('Sale Stge'[Amount K$]),FILTER(ALL('Sale Stge'[Sales_Stage]), 'Sale Stge'[Sales_Stage] >=3 && 'Sale Stge'[Sales_Stage] <=4)) )Sales All = IF(SELECTEDVALUE(info[Sales info])="Sales Stage", SUM('Sale Stge'[Amount K$]))Sales All = IF(SELECTEDVALUE(info[Sales info])="Sales Stage", SUM('Sale Stge'[Amount K$]))Budget = IF(SELECTEDVALUE(info[Sales info])="Budget",SUM(Budget[Amount K$]))
Remaing Budget = IF(SELECTEDVALUE(info[Sales info])="Budget", Budget[Budget] - [Sales M])
for Also give country as a slicer.
You could add a country fact table and then create the relationship with these three tables.
Result:
and here is pbix file, please try it..
Best Regards,
Lin