Gran Total by Subgroup Power BI

[John Jairo V]

Hi to all!

I need the Gran Total by Subgroup in Power BI. Specifically, I need the Gran Total Kilos Anticipado by NegocioPlanta. The correct value are given in the superior table in canvas ( 197964 is the total of Chicles assign to the material, is right ), but I need the value without putting the field NegocioPlanta into the table that has the materials (table down right). This is needed for get the % of Participation of the Material in his own NegocioPlanta.

Thx in advance for your answers.

 

[GraH - Guido]

Hi John, Chihiro will know better, but I think you can make your GranTotal measure in your dim table "dNegocioPlanta" first, then I expect filter context to handle the rest.

 

[Chihiro]

Can you show in screenshot where you want the value showing? It isn't clear to me what you want.

 

[John Jairo V]

Hi again! Thx for your answers!

This is what I need... but without putting NegocioPlanta into the table (putting NegocioPlanta I get good results). Blessings!

 

[Chihiro]

Looks like your main issue is that you have multiple relationship path between fRuana and dJerarquia.

Within your current context RELATED(COLUMNNAME) can't be used between the two tables.
I was curious and tried to follow through all your stepped measures, but had issue understanding relational logic used at each step.

This does not look like real-time dashboard type of scenario (i.e. using direct query mode). I'd strongly recommend restructuring your data model to star schema model (de facto standard for BI).

To me, it looks like tables are normalized for transactional application (i.e. data storage and retrieval), rather than business analysis application (i.e. querying & aggregating data). Data model should be designed so that they are unambiguous.

Have a read of link in below post.
https://chandoo.org/forum/threads/data-denormalization-and-linked-server.38253/

This is key to building reports in BI space.

Article on Star Schema.
https://learndatamodeling.com/blog/designing-star-schema/

Edit: Oh, and I noticed 1 many to many, bi-directional relationship in there. This should be avoided, unless there is specific use that it is required for, as it will prevent use of RELATED, and has different behaviour for ALL(). Have a read of link below.
https://docs.microsoft.com/en-us/power-bi/desktop-many-to-many-relationships

 

[John Jairo V]

Hi Chihiro! Thx in advanced for everything. I need to learn more about how to modelate some things... I'll try to change the things here. Really thx for your time. Blessings!