Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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 .

2 ACCEPTED SOLUTIONS
AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

bcdobbs
Super User
Super User

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 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

3 REPLIES 3
bcdobbs
Super User
Super User

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 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

@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

KT1598_3-1642183787372.png

KT1598_5-1642184193221.png

 

KT1598_6-1642184219732.png

 

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

KT1598_4-1642184164671.png

 

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

 

Sample.pbix 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.