Create Stacked Column Chart using only measures

[Anonymous]

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

[v-lili6-msft · Accepted solution]

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

[Anonymous]

Thank you it worked well!. i was using SELECTEDVALUE only for slicers. But today i learned usage of selectedvalue for tables. Thanks a lot 🙂

[Anonymous]

Chart should be as below: