Visualize two line chart with relative % of total including a slicer

[Anonymous]

Hi ,I'm trying to vizulize a trend chart line from  a table includes of 4 columns (Date (datetime),Sold(0,1),SaleType(retail/wholesale),Inspected(Yes/No)

Axis : Date

Legend:SaleType

Values:Sold

slicer:Inspected 

I'm trying to visualize the the percentage of Sold by Saletype in % of Total during date range that I can do this part 

My problem is when I apply filter of Inspected I'm trying to see how many Percent of Saletype is No/Yes inspected as of total sale I'm keep getting the Inspected percentage as of total of itself not from the total .

[AllisonKennedy · Accepted solution]

@Anonymous  In values, instead of using 'Sold' try a new measure:

Measure = DIVIDE ( [Sold] , CALCULATE( [Sold], ALL() ) )

That will give you the total % even when you filter for inspected. 

If you use ALLSELECTED instead of ALL you'll get closer to what you're getting now.

Hope that helps? I think I understood your question correctly...

[bcdobbs · Accepted solution]

Try this measure:

Measure = 
DIVIDE(
    [TotalSold],
    CALCULATE(
        [TotalSold],
        ALLSELECTED ( Sheet1[Date] ),
        REMOVEFILTERS ( Sheet1[Inspection] ),
        REMOVEFILTERS ( Sheet1[SaleType] )
    )
)

You also have an issue where your date is a text field so isn't sorting in correct order.

I separated things into dimensions and tidied it up which is available here:

Example File 

[AllisonKennedy · Accepted solution]

@Anonymous  In values, instead of using 'Sold' try a new measure:

Measure = DIVIDE ( [Sold] , CALCULATE( [Sold], ALL() ) )

That will give you the total % even when you filter for inspected. 

If you use ALLSELECTED instead of ALL you'll get closer to what you're getting now.

Hope that helps? I think I understood your question correctly...

[Anonymous]

@AllisonKennedy Thanks for the answer The formula gave me the following visualization the total as sold matches but when I apply filter as an inspection yes or no that does not match the table

Here's what numbers are like as a pivottable by count:

I need to display the following table as a percentage at the top

Sample.pbix