cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PatrickZFG Frequent Visitor
Frequent Visitor

Measure shows only total

Hello, 

 

I am making a dashboard where I want to calculate the average sales per day per product. 

 

I have one table ('EAN') that contains the article number [EAN13] and the title [Title short], and one table ('Sales/Day') that is structured as follows: 

  • [EAN] - Article number.
  • [Date] - Day (starting when the first sale of the first product occured, ending at the last day of the last month.
  • [First Day Stock] - Day when the product was for the first time in stock.
  • [Sellable] - True/false whether the [First Day Stock] is equal to or smaller than [Date].
  • [Voorraad] - Ending stock of that day.
  • [Verkopen] - Sales of that day ("Verkopen").
  • [Sellable 2] - Which indicates whether or not the product was in stock, and could potentially be sold.
  • The other columns can be ignored for now. 

 

BPI 1.png

I made a single-way relationship between the table of the article number/title, and the table as shown above.

 

With this format I want to achieve the following: calculate a rolling average of the sales per day. 

I made a measure for the all time sales per day:

 

 

Sales/Day All Time = 
        CALCULATE ( 
            SUM ( 'Sales/Day'[Verkopen] ) | 
            FILTER ( 
                ALLEXCEPT( 'Sales/Day' | 'Sales/Day'[EAN]) | 
                'Sales/Day'[Date] <= MAX ( 'Sales/Day'[Date] )
            )  |
            FILTER ( 
                ALLSELECTED ( EAN ) | 
                EAN[EAN13] <> BLANK()
            )
        )
        /
        CALCULATE ( 
            SUM ( 'Sales/Day'[Sellable 2] ) | 
            FILTER ( 
                ALLEXCEPT( 'Sales/Day' | 'Sales/Day'[EAN]) | 
                'Sales/Day'[Date] <= MAX ( 'Sales/Day'[Date] )
            ) |
            FILTER ( 
                ALLSELECTED ( EAN ) | 
                EAN[EAN13] <> BLANK()
            )
        )


However, when I make a table with the article numbers, and the sales/day measure, it only shows the average sales/day and this is the same for all articles. 

PBI2.PNG

 

If I would replace 'EAN'[EAN13] by 'Sales/Day'[EAN] in the row of the Matrix table, the result is correct; however, since I need other measures in the matrix table, I cannot use this way of working. 

PBI3.PNG
I hope somebody is able to help me out with this challange. If you need more information, please let me know. 

Best regards,

 

Patrick

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Measure shows only total

Hi @PatrickZFG ,

 

This as to do with the context of your measure, when you are making the visual based on the EAN table you cannot make the calculation based on the Sales[EAN] code because the row context is different, what happens is that you are making the filter context for the visual based on one value from one table but then calculating the measure on the Sales side.

 

This is even more pressing since you have a one to many relationship going from EAN to Sales so when you calculate a value from the Sales to the EAN since the relationship is going the other way is like you don't have any relation in place so it returns a single value for all the rows.

 

In your measure the allexcept part is redundant since the filter context comes from the EAN redo the measure to:

Sales/Day All Time = 
        CALCULATE ( 
            SUM ( 'Sales/Day'[Sales] ) ; 
            FILTER ( 
                 'Sales/Day'  ;
                'Sales/Day'[Date] <= MAX ( 'Sales/Day'[Date] )
            )  ;
            FILTER ( 
                ALLSELECTED ( EAN[EAN13] ) ; 
                EAN[EAN13] <> BLANK()
            )
        )
        /
        CALCULATE ( 
            SUM ( 'Sales/Day'[Sellable_1] ) ; 
            FILTER ( 
                 'Sales/Day' ;
                'Sales/Day'[Date] <= MAX ( 'Sales/Day'[Date] )
            ) ;
            FILTER ( 
                ALLSELECTED ( EAN[EAN13] ) ; 
                EAN[EAN13] <> BLANK()
            )
        )

As you can see below the measure Sales/Day All Time 2 (adjusted gives the correct value).

 

EAN.png

 

See attach PBIX file.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
Super User
Super User

Re: Measure shows only total

Hi @PatrickZFG ,

 

Measures are based on context and depending on the way you setup you measure you have different results, your model is not easy to replicate by hand but if you can share a mockup that would be great.

 

Even without making any mockup try to change the reference to EAN table on your allselected to the EAN13 column replace your measure by:

 

Sales/Day All Time = 
        CALCULATE ( 
            SUM ( 'Sales/Day'[Verkopen] ) | 
            FILTER ( 
                ALLEXCEPT( 'Sales/Day' | 'Sales/Day'[EAN]) | 
                'Sales/Day'[Date] <= MAX ( 'Sales/Day'[Date] )
            )  |
            FILTER ( 
                ALLSELECTED ( EAN[EAN13] ) | 
                EAN[EAN13] <> BLANK()
            )
        )
        /
        CALCULATE ( 
            SUM ( 'Sales/Day'[Sellable 2] ) | 
            FILTER ( 
                ALLEXCEPT( 'Sales/Day' | 'Sales/Day'[EAN]) | 
                'Sales/Day'[Date] <= MAX ( 'Sales/Day'[Date] )
            ) |
            FILTER ( 
                ALLSELECTED ( EAN[EAN13] ) | 
                EAN[EAN13] <> BLANK()
            )
        )

I have highlithed in bold the changes if this doesn't work please as I say before share some mockup file.

 

Regards,

MFelix 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
PatrickZFG Frequent Visitor
Frequent Visitor

Re: Measure shows only total

Hi @MFelix 

Thanks for your quick reply. Changing the measure formula does not seem to work unfortunately. 

 

I attached a mockup model for you. 

PBIX File

Excel file with data

 

Looking forward to hearing from you/

Thanks again for your help.

 

Patrick

 

Super User
Super User

Re: Measure shows only total

Hi @PatrickZFG ,

 

This as to do with the context of your measure, when you are making the visual based on the EAN table you cannot make the calculation based on the Sales[EAN] code because the row context is different, what happens is that you are making the filter context for the visual based on one value from one table but then calculating the measure on the Sales side.

 

This is even more pressing since you have a one to many relationship going from EAN to Sales so when you calculate a value from the Sales to the EAN since the relationship is going the other way is like you don't have any relation in place so it returns a single value for all the rows.

 

In your measure the allexcept part is redundant since the filter context comes from the EAN redo the measure to:

Sales/Day All Time = 
        CALCULATE ( 
            SUM ( 'Sales/Day'[Sales] ) ; 
            FILTER ( 
                 'Sales/Day'  ;
                'Sales/Day'[Date] <= MAX ( 'Sales/Day'[Date] )
            )  ;
            FILTER ( 
                ALLSELECTED ( EAN[EAN13] ) ; 
                EAN[EAN13] <> BLANK()
            )
        )
        /
        CALCULATE ( 
            SUM ( 'Sales/Day'[Sellable_1] ) ; 
            FILTER ( 
                 'Sales/Day' ;
                'Sales/Day'[Date] <= MAX ( 'Sales/Day'[Date] )
            ) ;
            FILTER ( 
                ALLSELECTED ( EAN[EAN13] ) ; 
                EAN[EAN13] <> BLANK()
            )
        )

As you can see below the measure Sales/Day All Time 2 (adjusted gives the correct value).

 

EAN.png

 

See attach PBIX file.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

PatrickZFG Frequent Visitor
Frequent Visitor

Re: Measure shows only total

Hi @MFelix,

 

You are a hero, it works perfectly! I marked your post as a solution! 

Have a great weekend.

 

Patrick

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 326 members 3,402 guests
Please welcome our newest community members: