ISINSCOPE Measure in Matrix Visual with Hierachy Filter

[BI_Flo]

Hi everyone,

I have a matrix visual representing a BOM (Bill of Materials) which was created with the PATH() function. Depending on the hierarchy level which ISINSCOPE=True in this matrix I want to display

• the sum needed for this particular Top Level Item --> Works ok:
  • Amount = if(ISINSCOPE(Tabelle11[Level 3]),CALCULATE(min(Tabelle11[Menge])),IF(ISINSCOPE('Tabelle11'[Level 2]),CALCULATE(sum(Tabelle11[Menge]),filter(Tabelle11,Tabelle11[Level 3]="")),IF(ISINSCOPE('Tabelle11'[Level 1]),CALCULATE(sum(Tabelle11[Menge]),filter('Tabelle11','tabelle11'[Level 2]="")))))​

• the amount needed of this part for all the Level 1 items--> Got this too with a static calculation in my excel input file

  • Correct Amount per Shipset = IF(ISINSCOPE('Tabelle11'[Level 3]),CALCULATE(sum('Tabelle11'[Menge S/S])),IF(ISINSCOPE('Tabelle11'[Level 2]),CALCULATE(sum('Tabelle11'[Menge S/S]),filter('Tabelle11','Tabelle11'[Level 3]="")),IF(ISINSCOPE('Tabelle11'[Level 1]),CALCULATE(sum('Tabelle11'[Menge S/S]),filter('Tabelle11','Tabelle11'[Level 2]="")))))

• the amount needed if this part for all Level 1 items dynamically adjusting with a slicer --> HERE IS THE PROBLEM

  • InScopeArtikelnummer = if(ISINSCOPE('Tabelle11'[Level 3]),CALCULATE(MIN('Tabelle11'[Art.Nr.])),if(ISINSCOPE(Tabelle11[Level 2]),CALCULATE(min(Tabelle11[Art.Nr.]),Tabelle11[Level 3]=""),if(ISINSCOPE('Tabelle11'[Level 1]),CALCULATE(MIN('Tabelle11'[Art.Nr.]),'Tabelle11'[Level 2]=""),1)))

    
    Amount per Shipset = IF(MIN('Tabelle11'[Assies])="Top-Assy",1,CALCULATE(sum('Tabelle11'[Menge]),filter(ALLSELECTED('Tabelle11'[Level 1],'Tabelle11'[Assies]),[InScopeArtikelnummer])))

I want to select a Top Assy in the Slicer Drawing Number and want to know how much sum([Menge]) of each single "Artikelnummer" is needed for selected Top Assys. Somehow the Measure "Amount per Shipset" only sums up the "Menge" of "Artikelnummer" for its Level.

Any advice?

I hope I was able to explain the problem clearly. If not - please do not hesitate to ask.

Thank you in advance,

Flo

[PaulDBrown]

@BI_Flo 

Thanks for the message and file. Can you provide a depiction of the expected output? it's hard to follow by reading extensive measures!

[BI_Flo]

Hi Paul,

this is how it should look like. If I apply the filter for "DRW100" the red total amount for DRW221 should change from 2 to 1. The Measure should look for the "Art.Nr" which is in a single distinctive column. 

Thanks and kind regards,

Flo

[PaulDBrown]

@BI_Flo 

Can you share a dummy PBIX file or dataset to work on?

[BI_Flo]

Sure!

Input and PBIX-File are accessible via this Dropbox Link:

https://www.dropbox.com/sh/j8c10leclg4gfhp/AACYAxFTD-UBdc8vk13-PCZqa?dl=0

Thank you in advance,

Flo