Power BI DAX

[suryamanoj52]

Hi, I have two columns Date and Total Sales. Now I need to split the Total Sales column into 3 columns. 

First is based on selected month and year I need total sales upto previous Financial year ending.

Second is based on selected month and year I need total sales from that Financial year starting to previous month.

Third is total sales for the selected month and year.

[RedaBi]

I encountered an issue with calculating the ratio in RLS. When RLS is enabled, the ratio calculation does not work correctly. To work around this problem, I created a separate table to calculate the sales ratio and linked it to a date table. However, the result is not correct. The amount 'Prélevement'[Facial] calculation is correct, but the ratio is incorrect. I tried multiplying the result by 100 to obtain the ratio as a percentage, but without success. Here is the measure used: 

Could you help me identify where the problem lies and how to correct the ratio calculation?

Ratio = 
SUMMARIZE(
    'Prélevement',
    'Prélevement'[Date Opération],
    'Prélevement'[Cd Dist], 
    'Prélevement'[Distrib],
    'Prélevement'[Facial],
    "Total Ventes", SUM('Prélevement'[Facial]),
    "Total Qte", SUM('Prélevement'[Qte Livree]),
    "Ratio Vente", DIVIDE(SUM('Prélevement'[Facial]),CALCULATE(SUM('Prélevement'[Facial]),ALL('Prélevement')))*100)

[lbendlin]

@RedaBi Please create a new thread. Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[koiralasmn]

''

[lbendlin]

@koiralasmn  please create a new post.  There is a difference between calculated columns and measures. Measures require some sort of aggregation like SUM()

[Ashish_Mathur]

Hi,

You have not specified the FY so i have assumed that it is Jan - Dec.  Try this process

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number
2. Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar table
3. To your slicer, drag Year and Month name from the Calendar Table and select any oen Year and Month name
4. Write these measures

Total sales = sum(Data[Sales])

Total sales in previous FY =  calculate([Total sales],previousyear(calendar[date]))

Total sales in present FY = calculate([Total sales],datesbetween(calendar[date],date(year(min(calendar[date]),1,1)),min(calendar[date])-1))

Hope this helps.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523