Last answered:

05 Apr 2023

Posted on:

05 Apr 2023

0

Error with Bar and Line Graph

Hello,


I had an issue on the previous lesson, where all my % Sales were 100%. I updated my DAX for the TotalSalesAmountAll to the below to fix.


TotalSalesAmountAll = CALCULATE([TotalSalesAmount], ALL(dim_SalesTeritory))

TotalSalesAmount = SUM(fact_InternetSales[SalesAmount])


Now when I select a currency, the Total Sales Amount in Chosen Currency equals the Total Sales Amount.

I have MonthName in my X-axis, TotalSalesAmountAll in the Column y-axis, and SalesAmountSelectedCurrency in line y-axis.


Please help.


5 answers ( 0 marked as helpful)
Instructor
Posted on:

05 Apr 2023

0

What does your DAX say about SalesAmountSelectedCurrency? This is the calculation that will determine what the selected currency will look like.

Posted on:

05 Apr 2023

0

Here's my DAX for SalesAmountSelected Currency

SalesAmountSelectedCurrency = 

var SalesAmount = SUM(fact_InternetSales[SalesAmount])

var USDollars = CALCULATE(SalesAmount,dim_Currency[CurrencyAlternateKey]="USD")

var CADDollars = CALCULATE(SalesAmount,dim_Currency[CurrencyAlternateKey]="CAD")

var GBP = CALCULATE(SalesAmount,dim_Currency[CurrencyAlternateKey]="GBP")

var AUD = CALCULATE(SalesAmount,dim_Currency[CurrencyAlternateKey]="AUD")

var DEM = CALCULATE(SalesAmount,dim_Currency[CurrencyAlternateKey]="DEM")

var FRF = CALCULATE(SalesAmount,dim_Currency[CurrencyAlternateKey]="FRF")

RETURN

IF(SELECTEDVALUE(dim_Currency[CurrencyAlternateKey])="USD", USDollars,
IF(SELECTEDVALUE(dim_Currency[CurrencyAlternateKey])="CAD", CADDollars,
IF(SELECTEDVALUE(dim_Currency[CurrencyAlternateKey])="GBP", GBP,
IF(SELECTEDVALUE(dim_Currency[CurrencyAlternateKey])="AUD", AUD,
IF(SELECTEDVALUE(dim_Currency[CurrencyAlternateKey])="DEM", DEM,
IF(SELECTEDVALUE(dim_Currency[CurrencyAlternateKey])="FRF", FRF,
"No Currency Selected"))))))

Instructor
Posted on:

05 Apr 2023

0

Ah, I can see the issue, your TotalSalesAmountAll is pulling SalesTeritory instead of All dim_Currency

Posted on:

05 Apr 2023

0

When I use TotalSalesAmountAll = CALCULATE([TotalSalesAmount], ALL(dim_Currency)), my graph looks good, but the table goes back to showing 100% for all %Sales.



On my test page, the %Sale is showing correctly on the card visual for each currency.


Instructor
Posted on:

05 Apr 2023

0

Please watch the lessons again for other discrepancies

Submit an answer