DAX Multiple column matches? Model attached

[carlenb]

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]

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]

Thanks @some_bih 

I updated from MAX to SUM in the DAX and this is what happens: 

1. Now the sum for each project is correct 
2. 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 · Accepted solution]

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] )

    )

[carlenb]

Fantastic, thanks a lot @some_bih for your patience 🙂 

[Greg_Deckler]

@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]

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]

@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

[carlenb]

Thanks @Greg_Deckler for the patience. I get it to work for for each project, supplier, year etc. But when I select multiple projects the sum is off. 

Example

The Power BI sales volume for all I projects is 27820 but it should be 38553, see picture below. I've linked the Power BI model and test data. Any ideas? 

Power BI: https://drive.google.com/file/d/1XDaL5TBVejoCArJo_-dUs-QxVaTrRliO/view?usp=sharing

Test data: https://docs.google.com/spreadsheets/d/1cJ-sHIwvC0pJjSYYznyVUzgGNrrF1mjn/edit?usp=sharing&ouid=11358...

Expected output 

Power BI output