Showing whole Company Stores Base with Sales Values in a way that allows Sales Values filtering by e.g. month

Question

I have two raw data tables:

Stores_base that contains detailed informations about all company stores
Sales_transactions that contains all registered sales transactions from all the stores that sold something:

Now, I need to create POWER BI dashboard that will allow final user to see whole store base with sales values for each store ( 0 or "-" for stores without any sales). The tricky part for me, is that there has to be possibiity to filter sales results by slicers added to the dashboard. So final result would look silimar to this (created in excel):

So far i only did this kind of transformations in excel, which is relativelly easy. But in Power BI i am not sure how to achive what i need.

I already have solid basics with Power Query data transformations in M Language and writing DAX measures - not very complexed though.

Agustin Palacios Agustin Palacios · Accepted Answer · 2020-12-09T00:15:55

Use the following DAX expression to achieve your goal:

Amount = 
VAR __sum = SUM( Sales_transactions[sales_amount] )
Return
    IF(
        NOT( ISBLANK( __sum ) ),
        __sum,
        "-" 
    )

The same for value:

Value = 
VAR __sum = SUM( Sales_transactions[sales_value] )
Return
    IF(
        NOT( ISBLANK( __sum ) ),
        __sum,
        "-" 
    )

This will allow you to show a "-" if there is no value and to keep your table if you select any filter.

Here are some examples:

Showing whole Company Stores Base with Sales Values in a way that allows Sales Values filtering by e.g. month

1 Answers