Matrix columns

[JMS1985]

Hello,

Im new to power bi so my question might be simple despite I´ve been searching for a way to solve it and I can´t find the right way to get a solution to my problem.

I have a table with dates and items and a table with items and value for each item per month.

I want to see how many items I had during a specific time range in the past and the cost of each item type and the total cost.

e.g. during dic2019 I had an average of 50 item A with a cost of 300€ and an average of 27 item B with a cost of 120€. Avg items are 127 and a cost of 420€

To get the average items I have created a quick measure. Sum of total items/count of days.

I have displayed this quick measure as the value for a matrix where I want to see the info. 

When I want to display the cost is where Im having trouble... I have created a column where I multiply the quick measure to the cost per month. However I cant get the info right.

Any idea or post I should read to solve this?

[AllisonKennedy]

It sounds like you'll need one more table that is just dates. A date table needs to have each date exactly once, otherwise the month aggregation won't work or will give strange results. 

If you can provide a bit more of your data sample that would be helpful to help the community give you a more clear picture of exactly how to make this work for your specific example. The average numbers you have mentioned don't add up to me, and it would be helpful to know if that is your desired result, or if that's already what's in your items table? 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[JMS1985]

Hello Allison, thanks for your reply and sorry for the little info I have provided.

These are the tables I have:

Date table. All dates between 2018 and 2021.

Value table. Where each vehicle has a code and each code has a unique value per month.

Item table. Where I have the number of vehicles with their code and the number of units per day.

Code table. where I have codes and families. I group vehicles into codes and codes into families.

The first column (sorry I cant display this info) shows Families and Codes.

The second column (Total vehicles dividido por Recuento de Dia) is the quick measure where I SUM all vehicles and divide them by the number of days selected on the date selector.

The third column is where I want to get the cost... In light grey Im able to get the cost for each code, but it doesnt SUM up right in the family total nor the general total.

thanks

[Icey]

Hi @JMS1985 ,

It is suggested to create a dummy .pbix file using dummy sample data for test. Please keep the structure of sample data same as your real data. You can upload your file to OneDrive for Business and then paste the link here.

Best Regards,

Icey

[AllisonKennedy]

What's the code for Total vehicles dividido por Recuento de Dia? 

Your data model ocnfuses me a bit still, it has a lot of double counting. We might be able to fix some of it with SUMX, or a few other tricks, but not sure yet. 

The value table should be the only one with Units or Cost in it, ideal, and should have the monthly cost and number Units and date and vehicle code.

Then you can relate that to the Date table and also relate it to the Code table, and the quick measures would behave a bit better.

However, because you have both value table and item table, these are two 'fact' tables as we call them, and getting them to interact properly in DAX requires a lot of thought and understanding of the context of DAX.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[JMS1985 · Accepted solution]

Thanks for your help @AllisonKennedy 

What's the code for Total vehicles dividido por Recuento de Dia?  

Total vehicles dividido por Recuento de Día =

DIVIDE(

    SUM('VEHICLE_OCCUPATION_AUDIT_VIEW'[Total vehicles]);

    COUNTA('Función invocada'[Día])

Regarding your other questions, Ill restart the work following your advices and see if it works.

Yesterday I kept trying other options but now I dont have the totals for each family nor the general total:

[Icey]

Hi @JMS1985 ,

Is this problem solved?

Best Regards,

Icey