DAX Multiple column matches? Model attached

Hi,

I have a Sales table from 2019 to 2022 with sales volume, sales date and suppliers.

See model here:

https://drive.google.com/file/d/1ie-tNNGnjq2jvL54EP1bJF2xWKNMK3AU/view?usp=sharing

Problem

There are five columns in the 2019-2022 sales table defining which project each sale should be attributed to. One sale could be relevant for more than one project, see the picture below. I am not sure on how to set up the model and the relationships correctly given that there are multiple columns. One possibility could maybe be to join the five columns to one column, where each id is separated by a comma? But if so, I'm still not sure about the solution.

Data overview (crossed over are not relevant to the problem/output)

Data

DimProject: each unique project id, project, sub-project
DimSupplier: Supplier name, supplier id
Sales 2019-2022: Sales volume, sales date, supplier id, article and then five (5) project id columns.
Calendar

Expected output

A graph with sales 2019-2022 (x-axis) and volume (y-axis), with possibility to filter each project, year and supplier. Example: The sales in 2019 for Project ID 22 (Project I and sub-project IB) is 12 466.

Thanks! Let me know if there are any questions.

some_bih
2 years ago
Hi carlenb please try measure below (include possible filtering on column Projekt)

Sales Total Measure =

VAR __Table_sum =

SUMMARIZE (

'Sales2019-2022',

[Sales date],

[Supplier],

[Supplier ID],

[Article],

"__SalesVolume", SUM ( [Sales volume] )

)

VAR __Result_sum =

SUMX ( __Table_sum, [__SalesVolume] )

RETURN

IF (

ISFILTERED ( DimProject[Projekt] ),

__Result_sum,

SUM ( 'Sales2019-2022'[Sales volume] )

)

8 Replies

Greg_Deckler
Community Champion
2 years ago
carlenb Typically in these sorts of situations, you unpivot your columns so that your 5 columns become 2 columns, Attribute and Value. The Attibute becomes the column name and the values, well, the values.
- carlenb
  Advocate II
  2 years ago
  Thanks Greg_Deckler
  
  I tried to unpivot and (almost) got it to work. It does return the correct value when I filter on projects, dates, suppliers etc. Yej! But if I leave it unfiltered it returns the wrong total sum, what am I missing? Please see below check in Excel (correct value) vs Power BI.
  
  Power BI
  - Greg_Deckler
    Community Champion
    2 years ago
    carlenb Right, so after unpivoting your Sales Volume numbers are duplicated so a simple sum will return inflated values. You can fix this with a measure like below. I'm not 100% understanding of your data or why it is organized how it is so there may be other approaches but this will fix the sum for the unpivoted data as you have it:
    
    Sales Total Measure = VAR __Table = SUMMARIZE('Table', [Sales date], [Supplier], [Supplie ID], [Article], "__SalesVolume", MAX([Sales volumn])) VAR __Result = SUMX(__Table, [__SalesVolume]) RETURN __Result
some_bih
Community Champion
2 years ago
Hi carlenb

In test data, there is wrong sum for second listed project ID and please change MAX to SUM as marked below and you should get expected output.
- carlenb
  Advocate II
  2 years ago
  Thanks some_bih
  
  I updated from MAX to SUM in the DAX and this is what happens:
  
  Now the sum for each project is correct
  However the total sum for all or multiple projects (for example if you select project I & project H the total sum will not be correct. When I use MAX as suggested by Greg_Deckler it is the other way around, so the total sum for all projects is correct, but not for each project
  See pictures below on what happens. Any ideas of how to get around this?
  
  From test data, the correct sum for all projects
  
  In Power BI using MAX for all projects (total is correct)
  
  In Power BI using SUM for all projects (total is not correct)
  
  In Power BI using SUM for specific projects (total is correct)
  - some_bih
    Community Champion
    2 years ago
    Hi carlenb please try measure below (include possible filtering on column Projekt)
    
    Sales Total Measure =
    
    VAR __Table_sum =
    
    SUMMARIZE (
    
    'Sales2019-2022',
    
    [Sales date],
    
    [Supplier],
    
    [Supplier ID],
    
    [Article],
    
    "__SalesVolume", SUM ( [Sales volume] )
    
    )
    
    VAR __Result_sum =
    
    SUMX ( __Table_sum, [__SalesVolume] )
    
    RETURN
    
    IF (
    
    ISFILTERED ( DimProject[Projekt] ),
    
    __Result_sum,
    
    SUM ( 'Sales2019-2022'[Sales volume] )
    
    )