Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Issue with the parameters in the Power BI App published for outside the organization

Hello All,   I have a requirement to publish a Power BI App outside my organisation. Our Power BI report is developed with 3 report parameters which will tell us user details viz., enrollment id, e...
Need Help
santoshlearner2's avatar
santoshlearner2
Icon for Resolver II rankResolver II
7 months ago

Dear All,

I have data which contains monthly sales values, in this you have the month the product name ( unique) / the vendor code / and the sales amount. In another table i have the dimcodes where it bifurcates the product further into categories and the vendor code into type of distributors, The issue is i get the total correct using the formula ( mentioned below) but then i put the category in the matrix the total does not match. 

ProductDistributor 1Distributor 2Distributor 3Distributor 4Distributor 5Total of thisThis total is correct . 
product 1474213492991111541200
product 23249386232169671006

 

Tables 1 ( which has the main data is ). Please note the dates are the same but each row item had product and vendor name, and keeps changing for eg in one line item we may have product 1 and vendor 1 in the second line we may have product 2 and vendor 1 and so on.

MonthProductVendor codeSales
31-12-2025product 1vendor 1100
31-12-2025product 2vendor 2200
31-12-2025product 3vendor 3300
31-12-2025product 4vendor 4400
    
31-12-2025product 1vendor 4500
31-12-2025product 2vendor 3600
31-12-2025product 3vendor 2700
31-12-2025product 4vendor 1800
    
31-12-2025product 4vendor 3200
31-12-2025product 3vendor 1250
31-12-2025product 2vendor 4300
31-12-2025product 1vendor 2600

Other table has dim codes like this

category for productsMain Category
product 4Books
product 3Books
product 2Pens
product 1Pens
  
category for VendorsMain Category
vendor 3Cat 2
vendor 1Cat 1
vendor 4Cat 2
vendor 2Cat 3

 

Dax measures for total sales is 

CALCULATE(sum(table1(sales), DATESMTD(DIMDatesQ[EOM])). I tried using sumx but gives and error.
 
Requesting your assitance please

 

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    7 months ago
    Total Sales MTD = //Try this might help you
SUMX(
    table1,
    CALCULATE(
        table1[Sales],
        DATESMTD(DIMDatesQ[EOM]),
        TREATAS(VALUES(DimProducts[Main Category]), table1[Product]),
        TREATAS(VALUES(DimVendors[Main Category]), table1[Vendor Code])
    )
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 months ago

    Hi,

    Cannot understand the question.  Share the download link of the PBI file.  Share the problem and expected result.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 months ago

    Hi santoshlearner2 
    I wanted to check if you had the opportunity to review the information provided by mh2587 . Please feel free to contact us if you have any further questions.


    Thank you.

  • Rajavenkatesh's avatar
    Rajavenkatesh
    New Member
    7 months ago

    Try this
    Total Sales MTD =
    VAR IsSingleVendor =
    HASONEVALUE(Table3[Vendor Category])
    RETURN
    CALCULATE(
    SUM(Table1[Sales]),
    DATESMTD(DIMDatesQ[Date]),
    IF(
    IsSingleVendor,
    VALUES(Table3[Vendor Category]),
    ALL(Table3[Vendor Category])
    )
    )