Dax Formula (Sum of Total Value) and 100% Stacked Chart

[Ciria]

Dears:

I have a question I don't know how resolve.

I have a Powerbi file with two excel files imported:

1. Receptions file.xls: This file shows all the receptions done from 2014 onwards. Every reception has its worth in euros and the quantity received.
2. OpenPO's.xls: This file shows all the purchase orders opened with suppliers, but not received yet. Every row has its worth (euros) and quantity too.
3. Both tables are many to many and fact tables. I mean, they both may have duplicates.
4. Both tables are linked each other by using a Vloop table (creating many to one relathionships) using Item Number as link.

By using CALCULATE function I am able to sum the total worth of every item, it looks something like this:

Calculate(sum(Receptionworth)+sum(Openpoworth))

By using this measure and item number, I am able to see per item the total expense (received + to receive).

Here goes my question:

I would like to create a 100% stacked chart that shows the percentage of the openPo againts the receptions worth, it should be something between 5-20% (depends on the item).

Could you help me out?

I don't know how to build this in powerbi.

Thanks in advance,

[kcantor]

You can do this by building your calculations in layers.

Instead of this: Calculate(sum(Receptionworth)+sum(Openpoworth))

I would create seperate calculations that way if one breaks, it is easier to find.

Reception Worth = SUM('Fact'[Receptionworth])

Open POs = SUM('Fact'[Openpoworth])

Total = [Reception Worth]+[Open POs]

% Open = DIVIDE([Open POs],[Total])

That is assuming your data is related and uses the column headers of Receptionworth and Openpoworth from the table labled Fact. Notice I only reference the table name in the first two calculations. You don't have to do that but it helps me keep track of where my data comes from.

Example data would be helpful even if it is just your table diagram.

[achinm45]

Hi @Ciria

Can you please post some sample data to bring more clarity ?

It will be helpful for us.

BR,

/Achin

[Ciria]

[Ciria]

I'm sorry, my reply doesn't show the text I included.

About the picture, please imagine they are two files (OpenPO.xls and Receptions.xls), with houndreds or thousands of rows. They are linked each other by using the vlookp table. As you can see the query structure is quite similar, but not all the columns match equally. I tried to simplify the example you require.

The aim to get a stacked graph that shows, per item, the % of the total€ from the OpenPo's againts the total € from the reception, being both the 100%.

In this example the % from OpenPO should be 50% of the total value (OpenPO+Receptions)

I am looking forward to hearing from you

Thanks in advance guys!

[kcantor]

@Ciria

The formulas I gave you should be valid. What you need to do is create a date table as well to use as a lookup table. Connect to both lookup tables. Then, populate your axis with the date table and pull in the values from the measures. The problem you are encountering seem to be coming from lack of a date table. Use the lookup tables to populate rows/axis and the values come from measures or calculations on your fact table. Otherwise you will not get clear values. Obviously you will need to use the correct table names in your measures where I reference 'Fact'.